Forum Discussion
Solved
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.
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.
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
8 Replies
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.
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.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
