Cumulative Total by Year

[RossS]

I have a table with a cumulative total which works well, but it continues to add year on year, instead of resetting each year.

What I currently have is this:

Running Total column =

CALCULATE (

    SUM ( 'Sales'[Sales Amount Actual] ),

    FILTER (

        ALL ( Sales ),

        Sales[Posting Date] <= MAX ( 'Sales'[Posting Date]

    )

))

And this produces the following table:

Year	Jan	Feb	March	April	May	June	July	August
2011	1	7	11	22	25	28	30	33
2012	35	38	41	44	46	47	48	49

I want it to look like this:

Year	Jan	Feb	March	April	May	June	July	August
2011	1	7	11	22	25	28	30	33
2012	2	5	8	11	13	14	15	16

My data goes from 2011 to present.

[tayloramy]

HI @RossS, 

Option A - Best practice with a Date table

1. Create a proper Date table and mark it as a Date table.

2. Use a simple YTD measure that resets each year.

-- Base measure
Sales Amount = SUM('Sales'[Sales Amount Actual])

-- Resets automatically by Year in the visual
Running Total YTD =
CALCULATE(
    [Sales Amount],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

If you prefer a one-liner, this works too:

Running Total YTD = TOTALYTD([Sales Amount], 'Date'[Date])

Option B - Keep your table, just stop clearing Year
Replace ALL(Sales) with ALLEXCEPT so the Year stays in context:

Sales Amount = SUM('Sales'[Sales Amount Actual])

Running Total By Year =
CALCULATE(
    [Sales Amount],
    FILTER(
        ALLEXCEPT('Sales', 'Sales'[Year]),
        'Sales'[Posting Date] <= MAX('Sales'[Posting Date])
    )
)

Tip: Make sure you’re using a measure (not a calculated column) for the running total.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[DataNinja777]

Hi @RossS ,

The problem with your current DAX formula is that the FILTER function is removing all existing filters on the 'Sales' table because of ALL(Sales). It then only applies a single condition: that the posting date is less than or equal to the maximum (or latest) posting date in the current context. This causes the calculation to sum all sales amounts from the very beginning of your data, leading to a cumulative total that continues to grow year after year instead of resetting.

To correct this and ensure the running total resets each year, you need to add another condition to your FILTER statement. This new condition will restrict the calculation to include only data from the current year being evaluated. By checking that the year of the 'Posting Date' is the same as the year of the maximum 'Posting Date' in the current context, you effectively restart the cumulative sum at the beginning of each new year.

Running Total by Year =
CALCULATE (
    SUM ( 'Sales'[Sales Amount Actual] ),
    FILTER (
        ALL ( 'Sales' ),
        'Sales'[Posting Date] <= MAX ( 'Sales'[Posting Date] )
            && YEAR ( 'Sales'[Posting Date] ) = YEAR ( MAX ( 'Sales'[Posting Date] ) )
    )
)

Alternatively, Power BI offers a more efficient and simpler solution for this common scenario using its built-in time intelligence functions. The TOTALYTD function is specifically designed to calculate a year-to-date total. It automatically handles the logic of summing values from the beginning of the current year up to the date specified in the current filter context, making your code cleaner and easier to read. For this function to work optimally, it is best practice to have a dedicated date table in your data model.

Running Total by Year (YTD) =
TOTALYTD ( SUM ( 'Sales'[Sales Amount Actual] ), 'Sales'[Posting Date] )

[johnt75 · Accepted solution]

Make sure that you have a date table linked to your sales table, and use columns from the date table in all your visuals.

Either mark the date table as a date table, or use the new calendar options to create a calendar. 

You can then write a measure like

Running Total =
CALCULATE ( SUM ( 'Sales'[Sales Amount Actual] ), DATESYTD ( 'Date'[Date] ) )