running sum of cumulative sum

[sundayDriver]

Date	Savings
July 3, 2020	1
July 10, 2020	2
August 2, 2020	1
August 9, 2020	3
September 1, 2020	2
October 3, 2020	4
October 4, 2020	1
November 1, 2020	2
December 4, 2020	2
January 1, 2021	3
January 5, 2021	1

MonthlyTotal = TOTALYTD(SUM([Savings]), [Date], ALL([Date]), "6/30")

Here’s the output I need to get:

Year	Month	Savings	MonthlyTotal	YearlyRunningTotal
2020	July	3	3	3
2020	August	4	7	10
2020	September	2	9	19
2020	October	5	14	33
2020	November	2	16	49
2020	December	2	18	67
2021	January	4	22	89

What I need help with is the calculation for YearlyRunningTotal.

[v-stephen-msft · Accepted solution]

Hi @sundayDriver ,

1.Create a Index column

Index = YEAR([Date])*100+[Date].[MonthNo]

 

2.Create measures

MonthlyTotal = 
CALCULATE (
    SUM ( 'Table'[Savings] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

YearlyRunningTotal = 
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Index],
            "M",
                CALCULATE (
                    SUM ( 'Table'[Savings] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) )
                )
        ),
        [M]
    ),
    FILTER ( ALL ( 'Table' ), [Index] <= MAX ( 'Table'[Index] ) )
)

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikelytics]

Dear @sundayDriver 

For running total of a year this should do it:

running total = TOTALYTD(Sum(t_7[Sales]),'Date'[Date],ALL('Date'[Date]),"12/31")

you can also change the end of year (last ttribute) to "06/30". if it does not work for you please try it in kombination with a date table.

 

 

Best regards

Mikelytics

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.