Power BI Cumulative Sum with Filters

Cumulative sums are easy to write badly because filter context is doing most of the work. The right pattern depends on whether the total should ignore the date slicer, respect it, or reset inside it. For broader reporting repair work, see the Power BI consultancy and ERP reporting analytics pages.

1Decide filter behaviour

The first question is not technical. Ask what users expect when they select a date range. Should the cumulative value start at the beginning of the year, or at the first selected date?

Other slicers, such as product, site or customer, usually need to stay active. Most cumulative-total issues come from removing too many filters.

• Use ALL when the date selection should not limit the start of the cumulative period.
• Use ALLSELECTED when the cumulative total should start inside the selected range.
• Use ALLEXCEPT or dimension filters only when you have a clear modelling reason.

2Use ALL for full-period totals

This pattern accumulates from the beginning of the available date table up to the current date. It is useful for lifetime totals or year-to-date patterns when combined with a year filter.

Order Quantity =
SUM ( Sales[Quantity] )

Cumulative Quantity =
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    [Order Quantity],
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= CurrentDate
    )
)

3Use ALLSELECTED for selected ranges

ALLSELECTED keeps the outer user selection and accumulates inside that selection. It is often better for exploratory reports where users intentionally select a shorter period.

This pattern should be tested carefully in matrices and small multiples because the visible context can differ from a simple line chart.

Cumulative Quantity Selected Range =
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    [Order Quantity],
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= CurrentDate
    )
)

Common mistakes to avoid

Validation checklist

• The start point of the cumulative total is agreed.
• Non-date slicers still work as expected.
• The result is tested with no date slicer and with a short selected range.
• A sample period reconciles to source data.
• The visual title explains whether the total is lifetime, YTD or selected-range.

FAQ

Why does my cumulative sum ignore slicers?

The measure may be using ALL on too broad a table. Limit filter removal to the date table or date column where possible.

Why does ALLSELECTED change totals in a matrix?

ALLSELECTED responds to the outer visual and slicer context. In complex visuals, that context is not always the same as a simple chart selection.