cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Luukvv93 Regular Visitor
Regular Visitor

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

 

ElementIDDatumVanafElementPrijsIDPrijsCalculatie

450721-3-20133950€ 0,028
450713-1-20145263€ 0,035
450725-2-20145290€ 0,037

 

ElementIDAantalDatum

45075.9807-11-2013
45074.5608-11-2013
45073.48014-1-2014
45071.49517-1-2014
45076.39524-1-2014
45078.98527-1-2014
45076.21029-1-2014
45071.49530-1-2014
45072.64031-1-2014
45071.4953-2-2014
45071.9105-2-2014
450718.0006-2-2014
45071.4957-2-2014
45075.21510-2-2014
45072.99011-2-2014
45072.30012-2-2014
450720.99013-2-2014
45075.52014-2-2014
45072.30017-2-2014
45071.68018-2-2014
45074.70019-2-2014
450718.00020-2-2014
45076.65521-2-2014
45072.99024-2-2014
45071.68025-2-2014
45072.40026-2-2014
45071.17627-2-2014
450725.55028-2-2014
45075.6303-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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating purchasing value having different purchasing prices per article

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

1.JPG

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:

2.JPG

here is pbix file,please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Calculating purchasing value having different purchasing prices per article

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

1.JPG

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:

2.JPG

here is pbix file,please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Luukvv93 Regular Visitor
Regular Visitor

Re: Calculating purchasing value having different purchasing prices per article

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