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 

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
    • 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.