cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Difference between two values from seperate dates with refresh option

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.

- J

1 ACCEPTED SOLUTION
Helper I

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
3 REPLIES 3
Helper I

Mark my post as a solution if it helped you 😀

Helper I

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
New Member

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 🙂