Rolling Total Month on Month

[Mewan117]

Hello All,

I currently have the following data in table form. I need to have the monthly totals as a rolling total and visualize it.  Eg: August would show 23+3569 and September will show 23+3569+23687. 

Current Visual 

Expected visual (example)

Thank you in advance

[Anonymous · Accepted solution]

Hi @Mewan117 ,

Please try:

Cumulative = CALCULATE (
    DISTINCTCOUNT(Total[orderId]), FILTER (ALLSELECTED(Total),[Date]<=MAX('Total'[Date])))

Corrcet Total of Cumulative = 
var _t= SUMMARIZE('Total',[Date],"Cum",[Cumulative])
return IF(HASONEVALUE(Total[Date]),[Cumulative],  SUMX(_t,[Cum]))

Output:

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

[tevisyauw]

@Mewan117 

try this :
Combine first month and year on new column

example new column name : MonthYear
and then 

Running Total = Calculate(SUM('Table1'[count of orderid]), filter(allselected('Table1'),'table1'[MonthYear] <= MAX('table1'[MonthYear])))

[Mewan117]

@tevisyauw Thank you for your response. That code didn't work, unfortunately. But I was able to use the following to get this output

Cumulative =

CALCULATE (

    DISTINCTCOUNT(Total[orderId]), FILTER (

        ALL ( 'Total' ),

        'Total'[Date].[Date]

            <= MAX ( 'Total'[Date].[Date] )))

But the totals are wrong. Also, my other slicers don't work. Eg below

It should only show the running total of the months that the slicer applies right? Eg for June it should be 76902+144373?

[tevisyauw]

@Mewan117 
For Slicer usually I make new table with Calendar Date

Date = CALENDAR(MIN('Total'[Date],MAX('Total'[Date]))

and
make something like this

Cumulative =

CALCULATE (

    DISTINCTCOUNT(Total[orderId]), FILTER (

        ALL ( 'Total' ),

        'Total'[Date] > FIRST('Calendar'[Date]) && 'Total'[Date]<= LAST('Calendar'[Date] )))

I am sorry, I am outside right now so i cannot test my measure. but usually I made the measure like this

[Mewan117]

@tevisyauw It didn't work. It gave me some strange numbers. I got close with this code

Cumulative =

CALCULATE (

    DISTINCTCOUNT(Total[orderId]), FILTER (

        ALL ( 'Total' ),

        'Total'[Date].[Date]

            <= MAX ( 'Total'[Date].[Date] )),

            FILTER(ALL('Total'),Total[companyId]<=MAX(Total[companyId])))

It works for some companyId's but not for all

Example of one that works (totals are a little different)

Example of one that doesn't work

I haven't figured out why this is happening.