Guides — Power BI Cumulative Sum With Filter

This guide gives UK SME teams a practical implementation path: start with the business question, check the data or workflow, build the smallest useful version, then document what has changed.

Book a data risk call View all services Power BI guide Home / Guides / Power BI

Build a Power BI cumulative sum that handles slicers and filters intentionally with ALL, ALLSELECTED or related DAX patterns.

2026-06-16 9 min read min read Digital Adaption On this page

Decide filter behaviour
Use ALL for full-period totals
Use ALLSELECTED for selected ranges

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.

Practical answer

Create the base measure, decide which filters should stay active, then use FILTER with ALL or ALLSELECTED over the date table to accumulate up to the current visible date.

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

2 Use 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
    )
)

3 Use 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

Using ALL on the fact table

That can remove customer, product or site filters and produce totals that do not match the user selection.

Not agreeing the slicer rule

A technically correct cumulative measure can still be wrong if users expect it to start at the selected date.

Mixing multiple date fields

Order date, invoice date and ship date create different cumulative curves. Use the date that matches the KPI.

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.

Need the report to reconcile with ERP?

Digital Adaption helps UK SMEs rebuild trusted Power BI, ERP and operational reporting after migrations, ownership gaps and model drift.

Review reporting trust

FAQ

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

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

Start with a 30-minute data risk call Start with a 30-minute data risk call

Power BI Cumulative Sum with Filters

Quick answer