Measure to Calculate Difference vs. Previous Period

[MarkDGaal]

Hello, I am pulling from a Data Source that appends new data once or twice a week (the duration between appending data is not consistent). When new data is appended to the table it is signified by a "Export Date & Time" field.

I would like to calculate the difference in the sum of one of the columns [Weighted Bookings] between two period of extracts. In Tableau I have the below delta/difference function that accomplishes my desired result after filtering the dataset for two periods of extracts.

Sum(ZN(SUM([Weighted Booking])) - LOOKUP(ZN(SUM([Weighted Booking])), -1))

For those not familiar the ZN function controls for nulls in Tableau by making them 0s. Thanks in advance.   

[Habib]

Hi @MarkDGaal

I am unable to understand your requirement. Also, seems like you are missing your date time reference in tablue formula when you copied here. Can you please elaborate requirement more for better understanding?

[MarkDGaal]

@Habib I echo your confusion on how tableau's measures work..... I believe the fromula I provided is created completely through the Tableau GUI and the way that you introduce time series to the measure is displayed below:

Note that you can also "Calculate the differece along:" "Table(Across)"

With respect to my PBI problem, I'm simply trying to find the difference between two sums of a column in my data when I have my dataset filtered for 2 periods. Forexample:

ID     Extract Date and Time    Value

1       01/01/2015 24:00:00      100

2       01/01/2015 24:00:00      100

1       12/12/2015 24:00:00      500

2       12/12/2015 24:00:00      500

The differnce in "Value" here between the 01/01 and 12/12 periods would be 800 (or (100+100)-(500+500))

[v-qiuyu-msft]

Hi @MarkDGaal,

In your scenario, you can create a measure like below to return difference between two dates:

diff = 
VAR MaxValue = CALCULATE( SUM( Table1[Value] ), FILTER( Table1, Table1[Value] = MAX( Table1[Value] ) ))
VAR MinValue = CALCULATE( SUM( Table1[Value] ), FILTER( Table1, Table1[Value] = MIN( Table1[Value] ) ))

return
(
	IF( MaxValue > MinValue, MaxValue - MinValue, MinValue - MaxValue )
)

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[MarkDGaal]

Hi @v-qiuyu-msft thanks and this makes sense and will work for my purposes.... but I suppose it feels a little bit more like a workaround then a true calculation of "from previous". Illutstarting, if I had more than 2 periods worth of data and wanted to show the difference between each period is their no way to do this using DAX unless I have defined the periods like Month-over-Month or YoY?  

For example:

If DAX can't be flexed this way does this then become an issue with the way my dataset is appended on each export?

[MarkDGaal]

@v-qiuyu-msft err perhaps I spoke too soon, your Diff Measure assumes that [Values] in each extract/export date and time will always be the same and therefore the filter's Min or Max is inconsequential. I've complicated my example a bit more in the attached as I was unable to figure out another way to filter that produced a desired result.

Solution should now be:
(99+100+300)-(600+500+400)=1001

 difference-two dates_MDGedits.pbix

[MarkDGaal]

@v-qiuyu-msft I was able to achieve the result of 1001 by filtering the table by the [Extract Date & Time] Column. See Below: 

diff = 
VAR MaxValue = CALCULATE( SUM( Table1[Value] ), FILTER( Table1, Table1[Extract Date & Time] = MAX( Table1[Extract Date & Time] ) ))
VAR MinValue = CALCULATE( SUM( Table1[Value] ), FILTER( Table1, Table1[Extract Date & Time] = MIN( Table1[Extract Date & Time] ) ))

return
(
 IF( MaxValue > MinValue, MaxValue - MinValue, MinValue - MaxValue )
)

My Question persists about what if I had more than 2 periods of data that I would like to do the "from previous" calculation

Is there any easy way to flex DAX to do that or is it limited to defined periods like YoY or MoM?

[v-qiuyu-msft]

Hi @MarkDGaal,

Do you mean the data table contain more than two period data? If that is a case, you can also use the measure which you tested:

diff01 = 
VAR MaxValue = CALCULATE( SUM( Table2[Value] ), FILTER( Table2, Table2[Extract Date and Time] = MAX( Table2[Extract Date and Time]) ) )
VAR MinValue = CALCULATE( SUM( Table2[Value] ), FILTER( Table2, Table2[Extract Date and Time] = MIN( Table2[Extract Date and Time]) ) )

return
MaxValue - MinValue

Best Regards,
Qiuyun Yu

[MarkDGaal]

@v-qiuyu-msft See the attached, what if my goal was to see the difference between each of the period (from the period previous to it). I believe some of DAX time intelligence functions would be helpful here; however, because the Extract Date & Time is not done at a consistent interval I'm not sure they can be leveraged....

difference-two dates_MDGedits02.pbix

As mentioned, Tableau makes this delta/difference calculation very easy where as PBI's only "Quick Clac" functionality is to show values as a % of the grand total. In the below image on the first row shows a difference of $2.097M for the period between 7/27/16 10:40:00AM and 7/28/2019 8:54:00AM and a difference of $0 for the subsequent period. 

[jahida]

This isn't the prettiest DAX, but it should get the job done for you:

LastExtr =
Var SecondToLast = MAXX(Table2, MAXX(FILTER(ALL(Table2[Extract Date and Time]), Table2[Extract Date and Time] < EARLIER(Table2[Extract Date and Time])), Table2[Extract Date and Time]))
return CALCULATE(SUM(Table2[Value]), ALL(Table2[Extract Date and Time]), Table2[Extract Date and Time] = SecondToLast)

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 should give you what I think you wanted, the difference between a given extract and the extract immediately before it.

The last if statement in diff01 just stops you from getting weird/garbage values for the oldest period in the document. If you'd like, you can replace it with just Last - [LastExtr]

[MarkDGaal]

@jahida everything works pretty well with your solution, thank you.... except the total's value going across the rows doesn't really make sense; for Cat1 for the period in your image the difference should be 401 not -100. Any soltion here or is this a limitation of the Matrix Visual?

Edit: Yes, it would have value, and yes 401 not -401.

[jahida]

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.

[jahida]

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:

[MarkDGaal]

@jahida I just walked away from my laptop but looking at your SecondToLastOrFirst variable... are you sure that would work if you have more than two periods of data selected? (eg. 3 extract dates?)

[jahida]

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:

[MarkDGaal]

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

[jahida]

Not sure what you mean so maybe a .pbix would be best.

[MarkDGaal]

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

[jahida · Accepted solution]

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:

[MarkDGaal]

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:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14476374-percent-change-percent-d...

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.

[MarkDGaal]

@jahida working through this now... out of curiousity for your SecondtoLast Variable... is there any benefit to using MAXX like you did....

 SecondToLast = MAXX(Table2, MAXX(FILTER(ALL(Table2[Extract Date and Time]), 
Table2[Extract Date and Time] < EARLIER(Table2[Extract Date and Time])), 
Table2[Extract Date and Time]))

vs.

SecondToLast= CALCULATE(MAX(REF_Opportunites[Export_Date_&_Time]), 
filter(REF_Opportunites, 
REF_Opportunites[Export_Date_&_Time]<>MAX(REF_Opportunites[Export_Date_&_Time])) )

?