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+23687. 

 

Current Visual 

 

Expected visual (example)

 

 

Thank you in advance

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. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 

    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'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