cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pawelj795
Post Prodigy
Post Prodigy

Difference between most recent value and last value

Hi,
I'm currently calculating the difference in Facebook likes between the last update and the most recent update.
Our updates vary, sometimes after 7 days, sometimes after 10 days, sometimes after 14 days.
 
How can I automate this case so that it works every time?
The breakdown by country is not important.


image.png

1 ACCEPTED SOLUTION

@pawelj795 - Sorry, I always mess up dates in non-US form.

 

Measure =
  VAR __MaxDate = MAX('Table'[Update Date])
  VAR __2ndDate = MAXX(FILTER('Table',[Update Date]<>__MaxDate),[Update Date])
  VAR __2ndValue = SUMX(FILTER('Table',[Update Date] = __2ndDate),[Likes])
  VAR __MaxValue = SUMX(FILTER('Table',[Update Date] = __MaxDate),[Likes])
RETURN
  __MaxValue - __2ndValue

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@pawelj795 - Not sure I 100% follow but I think:

 

Measure =
  (MAX([Update Date]) - MIN([Update Date]) * 1.

 

Kind of interested how you get fractional Likes. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler 
Thanks for weighing in.

Fractional likes come from rounding percentages 😉 They are not important.


I show below example of my desired results.
I want to calculate difference between value in blue circle and value in red circle.

 
 

image.png

 

@pawelj795 - Ah, you need Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

Basically, 

 

Measure =
  VAR __MinDate = MIN('Table'[Update Date])
  VAR __MaxDate = MAX('Table'[Update Date])
  VAR __MinValue = SUMX(FILTER('Table',[Update Date] = __MinDate),[Likes])
  VAR __MaxValue = SUMX(FILTER('Table',[Update Date] = __MaxDate),[Likes])
RETURN
  __MaxValue - __MinValue

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler 

I don't think that MIN is appropriate in this case.
 
I need the difference between the most recent value and the second recent value as I shown in the previous photo.
Look at my measure, I only need the last part.

image.png

@pawelj795 - Sorry, I always mess up dates in non-US form.

 

Measure =
  VAR __MaxDate = MAX('Table'[Update Date])
  VAR __2ndDate = MAXX(FILTER('Table',[Update Date]<>__MaxDate),[Update Date])
  VAR __2ndValue = SUMX(FILTER('Table',[Update Date] = __2ndDate),[Likes])
  VAR __MaxValue = SUMX(FILTER('Table',[Update Date] = __MaxDate),[Likes])
RETURN
  __MaxValue - __2ndValue

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler 
Thanks that works perfectly! I was exactly looking for that.

 

By the way, I have the same issue with US date format 😉

@pawelj795 - Sweet!

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!