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:

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

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

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

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

      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's avatar
        jahida
        Icon for Impactful Individual rankImpactful Individual
        10 years ago

        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]