Last 2 months difference measure

[abadi_89]

Hello all,

This is driving me crazy, I tried alot of stuff but it looks I can't do it.

I have this table:

CS Code	CS Name	Date	Value
CS01	Ketchup	1/6/2020	0.946375
CS01	Ketchup	1/2/2020	0.953944
CS01	Ketchup	1/3/2020	0.981028
CS01	Ketchup	1/4/2020	0.971444
CS01	Ketchup	1/5/2020	0.9645
CS01	Ketchup	1/1/2020	0.955278
CS01	Ketchup	1/7/2020	0.923116
CS01	Ketchup	1/8/2020	0.814521
CS01	Ketchup	1/9/2020	0.809767
CS01	Ketchup	1/10/2020	0.778958
CS01	Ketchup	1/11/2020	0.750706
CS02	Mayonnaise	1/1/2020	1.357695
CS02	Mayonnaise	1/2/2020	1.347019
CS02	Mayonnaise	1/3/2020	1.338747
CS02	Mayonnaise	1/4/2020	1.284948
CS02	Mayonnaise	1/5/2020	1.2665
CS02	Mayonnaise	1/6/2020	1.266955
CS02	Mayonnaise	1/7/2020	1.264721
CS02	Mayonnaise	1/8/2020	1.257416
CS02	Mayonnaise	1/9/2020	1.271779
CS02	Mayonnaise	1/10/2020	1.286299
CS02	Mayonnaise	1/11/2020	1.298247

I need a measure or column that can distinguish between the CS Codes (column 1) and it brings the difference between the last available date and the date before so the results should looks like:

CS01: 0.750706 - 0.778958 = -0.02825

CS02:  1.298247- 1.286299 = 0.011948

CS Code	CS Name	Date	Value	Differnce
CS01	Ketchup	1/11/2020	0.750706	-0.02825
CS02	Mayonnaise	1/11/2020	1.298247	0.011948

So it should recognize the last date (blue) and do a minus for the date before.

Thank you.

[FrankAT · Accepted solution]

Hi @abadi_89 

you can di it like this:

Measure = 
VAR _MaxDate = MAX('Table'[Date])
VAR _PreviousDate = CALCULATE(MAX('Table'[Date]),'Table'[Date] < _MaxDate)
VAR _ValueOfMaxDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_MaxDate = 'Table'[Date]))
VAR _ValueOfPreviousDate = CALCULATE(MAX('Table'[Value]),FILTER('Table',_PreviousDate = 'Table'[Date]))
RETURN
    _ValueOfMaxDate - _ValueOfPreviousDate

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[abadi_89]

Thank you Frank! simply genius.

Have a good weekend.