cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 🙂

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors