Difference between two values from seperate dates with refresh option

[jksl_12]

Hey there, 

I have been running into the issue that I have a dataset that consists of total values for various dates throughout the year that is reported into the same dataset. 

I want to set up a formula so that I take the latest date reported on and show the in or decrease in amount since last time. 

Date	Amount
December 1st	440.000
September 15th	478.000
July 16th	487.000

So in this setting, I want to take the data that will be coming on December 1st and have it subtracted with the September 15th data to show the difference. 

What I am however struggling with is how to make this update on its own so that next time a new date comes in how do I make sure it is the newest date that will get compared to the second to newest date?

There must be a clever way to write this in DAX or something, but I just really can't seem to find the correct answer to my problem.

Hope this question made sense, and thank you in advance.

- J

[dadb25]

Mark my post as a solution if it helped you 😀

[dadb25 · Accepted solution]

Hi jksl_12,

I think you want to have this result :

You can use this measure :

Dif with Max Date =
VAR MaxDate =
CALCULATE ( MAX ( 'Feuil2'[Date] ), ALL ( Feuil2[Date] ) )
VAR AmountMaxDate =
CALCULATE ( SUM ( 'Feuil2'[Amount] ), 'Feuil2'[Date] = MaxDate )
RETURN
SUM ( Feuil2[Amount] ) - AmountMaxDate

If a new date comes in your data, it will take its value.

Hope it helps 😀!

Regards

[jksl_12]

Thank you that does look like very much what I want! I will give it a try tomorrow because my brain is too fried to be doing it today😅
If it works as I suspect it will I will of course come back and give you a thanks for the help 🙂