Forum Discussion
pawelj795
Post Prodigy
Post ProdigyDifference 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...
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
Greg_Deckler
Community Champion
Community Championpawelj795 - Not sure I 100% follow but I think:
Measure =
(MAX([Update Date]) - MIN([Update Date]) * 1.
Kind of interested how you get fractional Likes.
pawelj795Greg_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.- Greg_Deckler
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- pawelj795I 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.