Forum Discussion

HEW's avatar
HEW
Icon for Helper III rankHelper 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...
HEW's avatar
HEW
Icon for Helper III rankHelper 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
Icon for Solution Sage rankSolution 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!