cancel
Showing results for
Did you mean:
Helper II

## 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

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

ElementIDAantalDatum

 4507 5.98 7-11-2013 4507 4.56 8-11-2013 4507 3.48 14-1-2014 4507 1.495 17-1-2014 4507 6.395 24-1-2014 4507 8.985 27-1-2014 4507 6.21 29-1-2014 4507 1.495 30-1-2014 4507 2.64 31-1-2014 4507 1.495 3-2-2014 4507 1.91 5-2-2014 4507 18 6-2-2014 4507 1.495 7-2-2014 4507 5.215 10-2-2014 4507 2.99 11-2-2014 4507 2.3 12-2-2014 4507 20.99 13-2-2014 4507 5.52 14-2-2014 4507 2.3 17-2-2014 4507 1.68 18-2-2014 4507 4.7 19-2-2014 4507 18 20-2-2014 4507 6.655 21-2-2014 4507 2.99 24-2-2014 4507 1.68 25-2-2014 4507 2.4 26-2-2014 4507 1.176 27-2-2014 4507 25.55 28-2-2014 4507 5.63 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!

1 ACCEPTED SOLUTION
Community Support

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

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

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

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.
Helper II

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

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors
Top Kudoed Authors