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:
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
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.