Next date of Previous date info met hulp van de DAX
functie EARLIER()
Als je de beschikking hebt over een tabel met onregelmatige periodieke
veranderingen van waardes, dan is het lastig om deze informatie te koppelen aan
een andere tabel.
Bijvoorbeeld: een tabel met kostprijsmutaties van producten
die op willekeurige data wijzigen.
Deze info wil je gebruiken om bijvoorbeeld een andere
tabel met de dagelijkse productie aantallen te voorzien van de juiste
kostprijs. Ik heb de beschikking over de tabel Price en een tabel Quantity
Ik wil de pricelist nu koppelen aan de kwantiteit om de waarde
van de productie aantallen te berekenen. Helaas is er geen unieke sleutel te
maken en dus moet het berekend worden.
Alvorens je dat kunt berekenen dient iedere rij een
begin- en einddatum te hebben.
De begindatum is al bekend, de kolom einddatum is als
volgt te berekenen:
NextDate=(CALCULATE(MIN(Price[date]);FILTER(Price;Price[date]>EARLIER(Price[date])&&Price[Category]=EARLIER([Category]))))
Als je de formule ontleed dan staat er eigenlijk:
Zoek de kleinste datum die groter is dan de huidige datum
(de eerstvolgende datum dus), waarbij de Category overeenstemt. De tabel ziet nu als volgt uit:
De lege datumvelden zijn in de rijen met de hoogste Date
per Category.
Om ook deze velden te vullen kun je hier eenvoudig een
extra berekend kolom met de datum van vandaag invullen als volgt.
NextDateAll =if(Price[NextDate]<>0;Price[NextDate]-1;now())
Je hebt nu een begin- en einddatum RANGE gecreëerd
waarmee je in de tabel Quantity de kostprijs kunt berekenen.
Eerst creëren we in de tabel Quantity een extra kolom om bij iedere productie
regel de juiste Price in de datum RANGE op te vinden als volgt:
Price = CALCULATE(VALUES(Price[Price]);FILTER(Price;Price[Category]=Quantity[Category]
&& Quantity[Date]>=Price[Date] && Quantity[Date]<=Price[NextDateAll]))
En daarna eenvoudig : Costprice =[Price]*[Quantity]
That's all!
Extra: PreviousDateAll
Mocht je op zoek zijn naar de datum van de vorige rij,
previous row, dan is deze als volgt te vinden:
PreviousDate=CALCULATE(MAX(Price[date]);FILTER(Price;Price[date]<EARLIER(Price[date];1)&&Price[Category]=EARLIER([Category])))
En vervolgens als er geen eerdere datum is, is de
begindatum gelijk aan de einddatum:
PreviousDateAll = if(Price[PreviousDate]<>0;Price[PreviousDate];Price[Date])