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. 

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.