Forum Discussion

Helper 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]), ...

Need Help

Show and Tell

Tips and Tricks

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.
SampleRunning.pbix173 KB
Ashish_Mathur
4 years ago
Hi,

You may download my PBI file from here.

Hope this helps.

jac88

Helper 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