Forum Discussion

jac88's avatar
jac88
Icon for Helper II rankHelper II
4 years ago
Solved

Running Sum is not working

Hello, 

 

I am trying to calculate the running sum, but it doesn't sum and not working. Can anybody please tell me what's wrong?

RunningSum = CALCULATE(SUM('Transaction'[ARR_Amount__c_M]),
                FILTER('Transaction',  'Transaction'[Effective_Date__c] <= MAX('Transaction'[Effective_Date__c])))
RunningSum2 = CALCULATE(SUM('Transaction'[ARR_Amount__c_M]),
                FILTER(ALL('Transaction'[Effective_Date__c]),
                    'Transaction'[Effective_Date__c] > MAX('Transaction'[Effective_Date__c])-365 &&
                    'Transaction'[Effective_Date__c] <= MAX('Transaction'[Effective_Date__c])))

 

Sample File - 

https://drive.google.com/file/d/12t6zlshfjGaWuYxZ5NIEM4-yArGXs1Y3/view?usp=sharing

 

 

Thank you so much

 

 

Need Help
Show and Tell
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    jac88 

    In order to use time intelligence like running totals you really want a dedicated calendar table in your model.  Then you can disable auto date/time.

    A calendar table can be make using DAX like this.  
    New Table:

    Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR ( [Date] ),
    "Month Year", EOMONTH( [Date] , -1) +1
)

    Disable auto date/time under File > Options > Current File:

    Then cahnge your running total measure like this.

    RunningSum2 = 
CALCULATE (
    SUM ( 'Transaction'[ARR_Amount__c_M] ),
    DATESINPERIOD ( Dates[Date], MAX ( 'Transaction'[Effective_Date__c] ), -1, YEAR )
)

    I have attached my updated sample file for you to look at.

     

     

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 years ago

    jac88 

    In order to use time intelligence like running totals you really want a dedicated calendar table in your model.  Then you can disable auto date/time.

    A calendar table can be make using DAX like this.  
    New Table:

    Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR ( [Date] ),
    "Month Year", EOMONTH( [Date] , -1) +1
)

    Disable auto date/time under File > Options > Current File:

    Then cahnge your running total measure like this.

    RunningSum2 = 
CALCULATE (
    SUM ( 'Transaction'[ARR_Amount__c_M] ),
    DATESINPERIOD ( Dates[Date], MAX ( 'Transaction'[Effective_Date__c] ), -1, YEAR )
)

    I have attached my updated sample file for you to look at.

     

     

    • jac88's avatar
      jac88
      Icon for Helper II rankHelper II
      4 years ago

      Ashish_Mathur  Hi, I was trying to get the rolling last 12 months running sum, and I found you have helped one of the people. I was using the same formula, but I don't think I am getting the same answer. Can you please take look if you have a free moment? I really appreciate your help. 

      1 Cummulative Value = 
CALCULATE (
    SUM ( 'Transaction'[ARR_Amount__c_M]),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)
      12 month running total = if(MAX('Calendar'[Date])<today(),BLANK(),AVERAGEX(CALCULATETABLE(SUMMARIZE('Calendar','Calendar'[Month],"ABCD",[1 Cummulative Value]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-11),MAX('Calendar'[Date]))),[ABCD]))

       

      https://drive.google.com/file/d/1MRCx7tdZ97D9JVMOqPwRf06B4ar_p4XP/view?usp=sharing 

       

      Again, thank you 

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        I see the running total for April 2022 onwards.  What is the question?  Please show the expected result very clearly in a simple Table format.