zaterdag 14 maart 2020




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])