Forum Discussion

HEW's avatar
HEW
Helper III
4 years ago

Calculate deviation% based on first available value

Hi.   I am trying to calculate the deviation% from the first available value compared to the last available value in selected period.   As you can tell from below matrix, the first row in the mat...
Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi Helen:

 

I'm wondering if you are able to share any data (data model layout) and sample data? I beleive you might need a disconnected table with item, price, price start date, price end date.

 

Thanks!

HEW's avatar
HEW
Helper III
4 years ago

Whitewater100 

 

I have tried to upload a test model ðŸ˜Š Hope it is working....

 

https://drive.google.com/drive/folders/1yNBHFdd9I40Tv7CFXCjV0y62GbRYlTda?usp=sharing

 

For article "Test 1" there is no purchasing value in the first month, March 2020, so I would like the start value for this article to be September 2021 as this is the first time we purchase this article. This will give us on increase on 2,06% (from 5.435 to  5.547).

 

I really appreciate that you are taking the time to help - thanks a lot.

Helen

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi Hew:

    OK. The first suggestion is to create a relationship between DATE[Date] with the MaterialTable {Valid From] Date.

    Then you can have several measures.

    Avg Price = AVERAGE(Historic_Gross_Price[Price])
    FirstValue = FIRSTNONBLANKVALUE('Date'[Date],[Avg Price])
    LastValue = LASTNONBLANKVALUE('Date'[Date],[Avg Price])
    New Diff = [LastValue] - [FirstValue]
    New Dev % =
    IF(HASONEVALUE(Historic_Gross_Price[Material]),
    DIVIDE([New Diff],[FirstValue],0))    note change format to percent 2 places. I hope this helps!