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
pawelj795Post Prodigy
Post ProdigyGreg_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
Community Champion
Community Championpawelj795 - 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.- Greg_Deckler
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- pawelj795
Greg_Deckler
Thanks that works perfectly! I was exactly looking for that.By the way, I have the same issue with US date format 😉