Calculating purchasing value having different purchasing prices per article

[Luukvv93]

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 the date where the price starts from.

ElementIDDatumVanafElementPrijsIDPrijsCalculatie

4507	21-3-2013	3950	€ 0,028
4507	13-1-2014	5263	€ 0,035
4507	25-2-2014	5290	€ 0,037

ElementIDAantalDatum

4507	5.980	7-11-2013
4507	4.560	8-11-2013
4507	3.480	14-1-2014
4507	1.495	17-1-2014
4507	6.395	24-1-2014
4507	8.985	27-1-2014
4507	6.210	29-1-2014
4507	1.495	30-1-2014
4507	2.640	31-1-2014
4507	1.495	3-2-2014
4507	1.910	5-2-2014
4507	18.000	6-2-2014
4507	1.495	7-2-2014
4507	5.215	10-2-2014
4507	2.990	11-2-2014
4507	2.300	12-2-2014
4507	20.990	13-2-2014
4507	5.520	14-2-2014
4507	2.300	17-2-2014
4507	1.680	18-2-2014
4507	4.700	19-2-2014
4507	18.000	20-2-2014
4507	6.655	21-2-2014
4507	2.990	24-2-2014
4507	1.680	25-2-2014
4507	2.400	26-2-2014
4507	1.176	27-2-2014
4507	25.550	28-2-2014
4507	5.630	3-3-2014

I need your help on defining a measure that calculates the purchase value (aantal * prijscalculatie) in the related timeframe. Would really appreciate your help!

[v-lili6-msft · Accepted solution]

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

[Luukvv93]

Amazing, exactly what I needed. Thanks fo much Lin @v-lili6-msft !