Forum Discussion

Luukvv93's avatar
Luukvv93
Helper II
7 years ago
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 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!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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