Forum Discussion
Measure to Calculate Difference vs. Previous Period
Here's my result:
There's a 20k discrepency in one spot but I doubt that's something wrong with the formulas, seems like a data discrepency... Other than that matches perfectly. If you want the last column to appear blank, here's a slightly adapted LastExtr formula:
LastExtr =
Var SecondToLastOrFirst = IF(HASONEVALUE(Sheet1[Extract Date and Time]), CALCULATE(MAX(Sheet1[Extract Date and Time]), FILTER(ALL(Sheet1[Extract Date and Time]), Sheet1[Extract Date and Time] < MAX(Sheet1[Extract Date and Time]))), MIN(Sheet1[Extract Date and Time])
)
return IF(SecondToLastOrFirst < CALCULATE(MIN(Sheet1[Extract Date and Time]), ALLSELECTED(Sheet1[Extract Date and Time])), BLANK(), CALCULATE(SUM(Sheet1[Value]), ALL(Sheet1[Extract Date and Time]), Sheet1[Extract Date and Time] = SecondToLastOrFirst))diff01 =
VAR Last = CALCULATE( SUM( Table2[Value] ), FILTER( Table2, Table2[Extract Date and Time] = MAX( Table2[Extract Date and Time]) ) )return
if([LastExtr] & "" = BLANK(), BLANK(), Last - [LastExtr])Then diff01 holds the change numbers you want.
And then you'd have to set the Extract Date and Time field to "Show items with no data". Here's that result:
Yeah the total basically just gives the same value as the latest date. You're right, best to hide it. You'd have to write a separate case in the measure (if there's more than one date in the current context, probably using HASONEVALUE) in order to get a result like 401 (I feel like it should be positive but could be wrong). I can experiment if that's useful to you, but I'm glad you found the rest of it useful.
EDIT: Your edit makes it seem like this is a decent priority to you, so I'll give it a shot.
How's this as an updated version of LastExtr (the other one doesn't have to change):
LastExtr =
Var SecondToLastOrFirst = IF(HASONEVALUE(Table2[Extract Date and Time]), CALCULATE(MAX(Table2[Extract Date and Time]), FILTER(ALL(Table2[Extract Date and Time]), Table2[Extract Date and Time] < MAX(Table2[Extract Date and Time]))), MIN(Table2[Extract Date and Time])
)
return CALCULATE(SUM(Table2[Value]), ALL(Table2[Extract Date and Time]), Table2[Extract Date and Time] = SecondToLastOrFirst)
Here's the result it gives now:
Here's my result:
There's a 20k discrepency in one spot but I doubt that's something wrong with the formulas, seems like a data discrepency... Other than that matches perfectly. If you want the last column to appear blank, here's a slightly adapted LastExtr formula:
LastExtr =
Var SecondToLastOrFirst = IF(HASONEVALUE(Sheet1[Extract Date and Time]), CALCULATE(MAX(Sheet1[Extract Date and Time]), FILTER(ALL(Sheet1[Extract Date and Time]), Sheet1[Extract Date and Time] < MAX(Sheet1[Extract Date and Time]))), MIN(Sheet1[Extract Date and Time])
)
return IF(SecondToLastOrFirst < CALCULATE(MIN(Sheet1[Extract Date and Time]), ALLSELECTED(Sheet1[Extract Date and Time])), BLANK(), CALCULATE(SUM(Sheet1[Value]), ALL(Sheet1[Extract Date and Time]), Sheet1[Extract Date and Time] = SecondToLastOrFirst))diff01 =
VAR Last = CALCULATE( SUM( Table2[Value] ), FILTER( Table2, Table2[Extract Date and Time] = MAX( Table2[Extract Date and Time]) ) )return
if([LastExtr] & "" = BLANK(), BLANK(), Last - [LastExtr])Then diff01 holds the change numbers you want.
And then you'd have to set the Extract Date and Time field to "Show items with no data". Here's that result:
Okay, phew took a little debugging but you solution(s) work great. I think next time I'll stick with time intelligence functions and forget about the users who want to custom select [Export Date and Time], but this was a really usefull exercise.
As a note, for those looking for similar funcaitonality and/or expansion of the "Quick Calc" functionality the idea is suggested here:
EDIT: jahida you may want to edit your last post (solution) to include the [diff01] measure as that was in a previous potential solution. Thanks Again.
Pretty sure, probably worth some additional testing (a bigger dataset than the one you provided) but I think it would be fine.
I threw in a tad more data and it works fine:
jahida I'm getting some odd values.... if I store the SecondToLastOrFirst Variable as a Measure then it returns the MIN() of my Export Date & Times.
Does you solution continue to work if you add more data prior to the dates/periods you have selected (and then don't select those dates)? If so, let me know and I'll produce a .pbix we can both work from duplicating my issues.
Not sure what you mean so maybe a .pbix would be best.
This is too many rows to paste into a Pbix data table, for similicities sake can you see if your method will work with the linked excel document as the data source?
ChangefromPervious_sampledata.xlsx
You result should look like this for Cat1, Cat2, and Cat3 respectively with the following 5 most recent periods selected:
Or in Total.... Cat1 = ($530,242) Cat2= $769,142 and Cat3 = $300,000
