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

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.

      • DimMich's avatar
        DimMich
        Regular Visitor
        7 years ago

        Thank you Mariusz that worked for me.