Forum Discussion

Mewan117's avatar
Mewan117
Frequent Visitor
3 years ago
Solved

Rolling Total Month on Month

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

Mewan117's avatar
Mewan117
Frequent Visitor
3 years ago

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's avatar
tevisyauw
Icon for Helper I rankHelper I
3 years ago

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's avatar
    Mewan117
    Frequent Visitor
    3 years ago

    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.