Forum Discussion

MarkDGaal's avatar
MarkDGaal
Helper III
10 years ago
Solved

Measure to Calculate Difference vs. Previous Period

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.   

Need Help
  • jahida's avatar
    jahida
    10 years ago

    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:

22 Replies

  • Habib's avatar
    Habib
    Continued Contributor
    10 years ago

    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's avatar
      MarkDGaal
      Helper III
      10 years ago

      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's avatar
        v-qiuyu-msft
        Community Support
        10 years ago

        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