Forum Discussion
Solved
Calculating purchasing value having different purchasing prices per article
Hello community, I need to calculate the purchasing value of the ElementID below. The problem I am facing is that the element has different prices over time (PrijsCalculatie). DatumVanaf is t...
hi, Luukvv93
You could use this way as below:
Step1:
Add an enddate column for each price of ElementID in Price dim table
Enddate = IF( CALCULATE(MIN(Price[DatumVanaf]),FILTER(Price,Price[ElementID]=EARLIER(Price[ElementID])&&Price[DatumVanaf]>EARLIER(Price[DatumVanaf])))=BLANK(),DATE(9999,12,31),CALCULATE(MIN(Price[DatumVanaf]),FILTER(Price,Price[ElementID]=EARLIER(Price[ElementID])&&Price[DatumVanaf]>EARLIER(Price[DatumVanaf]))))
Step2:
Now use this formula to create a measure
Measure =
var _table=ADDCOLUMNS('Table',"Price",CALCULATE(SUM(Price[PrijsCalculatie]),FILTER(Price,'Table'[ElementID]=Price[ElementID]&& 'Table'[Datum]>=Price[DatumVanaf]&&'Table'[Datum]<Price[Enddate]))) return SUMX(_table,[Aantal]*[Price])Result:
here is pbix file,please try it.
Best Regards,
Lin
hi, Luukvv93
You could use this way as below:
Step1:
Add an enddate column for each price of ElementID in Price dim table
Enddate = IF( CALCULATE(MIN(Price[DatumVanaf]),FILTER(Price,Price[ElementID]=EARLIER(Price[ElementID])&&Price[DatumVanaf]>EARLIER(Price[DatumVanaf])))=BLANK(),DATE(9999,12,31),CALCULATE(MIN(Price[DatumVanaf]),FILTER(Price,Price[ElementID]=EARLIER(Price[ElementID])&&Price[DatumVanaf]>EARLIER(Price[DatumVanaf]))))
Step2:
Now use this formula to create a measure
Measure =
var _table=ADDCOLUMNS('Table',"Price",CALCULATE(SUM(Price[PrijsCalculatie]),FILTER(Price,'Table'[ElementID]=Price[ElementID]&& 'Table'[Datum]>=Price[DatumVanaf]&&'Table'[Datum]<Price[Enddate]))) return SUMX(_table,[Aantal]*[Price])
Result:
here is pbix file,please try it.
Best Regards,
Lin
Amazing, exactly what I needed. Thanks fo much Lin v-lili6-msft !