Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MarkDGaal
Helper III
Helper III

Measure to Calculate Difference vs. Previous Period

‎07-27-2016 12:31 PM

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.   

Labels:
Message 1 of 23
30,961 Views
1 ACCEPTED SOLUTION
jahida
Impactful Individual
Impactful Individual
In response to MarkDGaal

‎08-05-2016 08:43 AM

Here's my result:

 

Capture.PNG

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

View solution in original post

Message 22 of 23
33,999 Views
22 REPLIES 22

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All