Forum Discussion

MarkDGaal's avatar
MarkDGaal
Icon for Helper III rankHelper 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 ...
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:

Habib's avatar
Habib
Icon for Continued Contributor rankContinued 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
    Icon for Helper III rankHelper 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
      Icon for Community Support rankCommunity 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

      • MarkDGaal's avatar
        MarkDGaal
        Icon for Helper III rankHelper III
        10 years ago

        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?