Forum Discussion

DimMich's avatar
DimMich
Regular Visitor
7 years ago
Solved

Cumulative running total

Hello

 

i have a column named "counting" at a table named "TableTest" that register a number at a certain date of the year and some negative registrations. The matrix is like this:

i want to create a cumulative running total based on date. When i try the quick messure running total solution

RunningTotal =
CALCULATE(
    SUM('Mortality_color'[counting]);
    FILTER(
        ALLSELECTED('TableTest'[Date].[Day]);
        ISONORAFTER('TableTest'[Date].[Day]; MAX('TableTest'[Date].[Day]); DESC)
    )
)
 
i have the running total, but is only for the month (resets and start from the begin on June)
 
How i can achive to be continious?
Need Help
Show and Tell
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi DimMich 

    Please try the below.

    RunningTotal =
CALCULATE(
    SUM('Mortality_color'[counting]);
    FILTER(
        ALLEXCEPT('TableTest'; TableTest[YearColumn]);
        TableTest'[Date] <= MAX('TableTest'[Date])
    )
)


    Regards,
    Mariusz

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

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi DimMich 

     

    Use the below instead.

    RunningTotal =
CALCULATE(
    SUM('Mortality_color'[counting]);
    FILTER(
        ALL('TableTest');
        TableTest'[Date] <= MAX('TableTest'[Date])
    )
)

    Regards,
    Mariusz

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

    • DimMich's avatar
      DimMich
      Regular Visitor
      7 years ago

      Hello Mariusz

       

      Thank you for your reply.

       

      The new syntax correct it  and now its continious for all dates.

       

      But this create a new problem  because i wanted to have the running total reset every year.

      I attach a picture how is now with your syntax.

       

      Can you propose me a solution on this?

       

      Thank you in advance

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi DimMich 

        Please try the below.

        RunningTotal =
CALCULATE(
    SUM('Mortality_color'[counting]);
    FILTER(
        ALLEXCEPT('TableTest'; TableTest[YearColumn]);
        TableTest'[Date] <= MAX('TableTest'[Date])
    )
)


        Regards,
        Mariusz

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