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.