Cumulative Total by Year

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] )