Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference in cummulative total

Hi DAX experts,

I currently have this measure that calculates running total for payment based on date

Total Running Payment = 
VAR MaxDate = MAX ( 'Balance'[new_date] ) 
RETURN
    CALCULATE (
        [Total Payment Amount],            
        'Balance'[new_date] <= MaxDate,   
        ALL ( Balance )               
    )

How do I proceed to find the differences between running total? I tried out several solutions posted in the forum but wasnt able to get it working. I would really appreciate for your input. Thanks!

Need Help
Show and Tell
Tutorial Requests
  • edhans's avatar
    edhans
    6 years ago

    You will need to find out the next to last date. So your variable 

    VAR MaxDate = MAX ( 'Balance'[new_date] )

     is fine. You can use that as follows:

    VAR varMaxDate =
    MAX( Dates[Date] )
VAR varNextToMaxDate =
    MAXX(
        FILTER(
            ALL( Dates[Date] ),
            Dates[Date] < varMaxDate
        ),
        Dates[Date]
    )

    So in my little sample table, varMaxDate is April 1, and varNextToMaxDate is March 15. You can then use the varNextToMaxDate to calculate the previous total.

     
     
     
     

    So perhaps another variable that calculates that previous total, then just do the math: varMaxValue - varPreviousDateValue.

        CALCULATE (
        [Total Payment Amount],            
        'Balance'[new_date] = varNextToMaxDate,   
        ALL ( Balance )               
    )
     

    But I'd need to see some data to tinker with the filtering. I think your ALL() statement takes care of that but you'd need to test.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    You can also try this since you are using time intelligence and a date table.

     

    Running Total = 
VAR MaxDate = MAX ( 'Dates'[Date] ) 
RETURN
    CALCULATE (
        [Total Sales],           
        'Dates'[Date] <= MaxDate,   
        ALL ( Dates )               
    )

     

    Running Total-1 = 
VAR MaxDate = MAX ( 'Dates'[Date] ) - 1 
RETURN
    CALCULATE (
        [Total Sales],           
        'Dates'[Date] <= MaxDate,   
        ALL ( Dates )               
    )

     

    Difference RT = [Running Total] - [Running Total-1]

     

     

     

     

     

    Regards,
    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak,

      Thanks for responding. I want to find the difference in running total in two consecutive dates

      So for 3/31/20, the difference would be between 773,541,925 and 773,087,384; for 3/30/20 the difference would be between 773,087,386 and 772,220,532; etc.

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    You will need to find out the next to last date. So your variable 

    VAR MaxDate = MAX ( 'Balance'[new_date] )

     is fine. You can use that as follows:

    VAR varMaxDate =
    MAX( Dates[Date] )
VAR varNextToMaxDate =
    MAXX(
        FILTER(
            ALL( Dates[Date] ),
            Dates[Date] < varMaxDate
        ),
        Dates[Date]
    )

    So in my little sample table, varMaxDate is April 1, and varNextToMaxDate is March 15. You can then use the varNextToMaxDate to calculate the previous total.

     
     
     
     

    So perhaps another variable that calculates that previous total, then just do the math: varMaxValue - varPreviousDateValue.

        CALCULATE (
        [Total Payment Amount],            
        'Balance'[new_date] = varNextToMaxDate,   
        ALL ( Balance )               
    )
     

    But I'd need to see some data to tinker with the filtering. I think your ALL() statement takes care of that but you'd need to test.