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:

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]

 

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

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])) )

?

 

 

  • jahida's avatar
    jahida
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    Probably not, just that I'm not great at using the Calculate function. Your version looks fine, and probably more efficient (except maybe needing an ALL on the Extract Date and Time column, not sure about that. If you did use an all, you'd need to change the <> to <).