Running Sum is not working

[jac88]

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

[jdbuchanan71 · Accepted solution]

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

@Ashish_Mathur  & @jdbuchanan71  Thank you so much. I really appreciate your help. 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[jac88]

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

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.

[jdbuchanan71 · Accepted solution]

@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.