Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
jksl_12
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. 

 

DateAmount
December 1st440.000
September 15th478.000
July 16th487.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

1 ACCEPTED SOLUTION
dadb25
Helper I
Helper I

Hi jksl_12,

I think you want to have this result :

dadb25_0-1701269851086.png

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

View solution in original post

3 REPLIES 3
dadb25
Helper I
Helper I

Mark my post as a solution if it helped you 😀

dadb25
Helper I
Helper I

Hi jksl_12,

I think you want to have this result :

dadb25_0-1701269851086.png

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

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 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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