Power BI Cumulative Totals Resetting Unexpectedly: DAX Fixes That Actually Work

You build a running total measure, drop it on a line chart, and it looks perfect for the first few rows. Then it resets at a month boundary, or collapses to a single-day value when a slicer changes, or silently gives you a cumulative sum that's identical to the regular sum. The measure isn't broken in an obvious way — it just isn't cumulative.

This is one of the most common DAX problems, and it almost always comes down to filter context. Once you understand what's happening, the fixes are straightforward.

What You'll Learn

• Why DAX filter context causes cumulative totals to reset or produce wrong values
• The standard running total pattern and its failure modes
• How to choose between ALL, ALLSELECTED, and FILTER for cumulative logic
• How to handle cumulative totals across categories, not just dates
• Pitfalls that only appear after a slicer or cross-filter is applied

Prerequisites

You should be comfortable writing basic DAX measures and understand what CALCULATE does at a high level. You'll need a date table in your model marked as a date table, with a continuous date column. If your date table has gaps, several of these patterns will produce incorrect results.

How DAX Filter Context Breaks Cumulative Totals

Every DAX measure evaluates inside a filter context. When your visual has a date axis, each row in that visual is filtered to a single date — or a single month, week, or year depending on the level of granularity you've chosen. The measure sees only that slice of data.

A cumulative total needs to see all dates up to and including the current one. That means you have to explicitly expand the filter context to include everything before the current date, while still anchoring to "the current date" from the visual's perspective. When either of those two things goes wrong, the total resets or returns a non-cumulative result.

This is the same root cause as several other DAX anomalies — if you've hit situations where DAX measures show blank instead of zero, you'll recognize the filter context fingerprint immediately.

The Classic Running Total Pattern (and Why It Fails)

The standard template for a running total looks like this:

Cumulative Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL(Dates[Date]),
        Dates[Date] <= MAX(Dates[Date])
    )
)

This works in isolated tests. You put it on a table visual with a date column and the numbers climb correctly. Then a user applies a slicer, or you move the visual to a page with a report-level filter, and the cumulative total either resets to a per-period value or ignores the slicer entirely.

The problem: ALL(Dates[Date]) removes every filter from the date column, including any slicer the user has applied. If someone selects March through June in a date slicer, ALL blows past that and sums across all years. That's not what users expect from a cumulative total.

Fix 1: Use ALLSELECTED Instead of ALL

Replacing ALL with ALLSELECTED is usually the first and most important fix. ALLSELECTED removes the filter from the current visual context (the row being evaluated) but keeps any filters the user has applied from outside the visual — slicers, page filters, and report filters.

Cumulative Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALLSELECTED(Dates[Date]),
        Dates[Date] <= MAX(Dates[Date])
    )
)

Now if a user selects March through June, the cumulative total respects that range. The first date in the selection becomes the starting point, and the measure climbs correctly through June without touching January or February data.

Use ALLSELECTED as your default. Only fall back to ALL if you have an explicit requirement that the running total should always start from the absolute beginning of your data, regardless of any user selection.

Fix 2: Anchor the Date with MAX Instead of a Fixed Parameter

A subtler failure mode appears when you switch from a date-level visual to a month or quarter level. At the month level, MAX(Dates[Date]) returns the last date in the month that's currently being evaluated. That's the behavior you want. But if you've written your measure to accept a hard-coded cutoff — or if another developer passed in a scalar date variable — the anchor can stop moving correctly.

Always derive the anchor dynamically from the current filter context:

Cumulative Sales =
VAR CurrentDate = MAX(Dates[Date])
RETURN
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(
            ALLSELECTED(Dates[Date]),
            Dates[Date] <= CurrentDate
        )
    )

Storing MAX(Dates[Date]) in a variable first is both clearer and slightly more efficient — DAX evaluates it once per row rather than once per row of the filtered table. It's a small win, but it's also cleaner to read when you come back to the measure six months later.

Fix 3: Handle Missing Dates with LASTDATE or CALCULATE + FILTER

If your fact table has days with no transactions, the date axis in your visual may show gaps. Depending on your visual settings and whether the date table is truly continuous, MAX(Dates[Date]) inside a filter context with no matching fact rows can return a blank, which breaks the comparison in the FILTER.

One approach is to wrap the entire measure in an IF(HASONEVALUE(...), ...) guard, but that hides the problem rather than solving it. A cleaner fix is to use LASTDATE when you need to resolve a date from a potentially sparse context:

Cumulative Sales =
VAR CurrentDate = LASTDATE(Dates[Date])
RETURN
    IF(
        NOT ISBLANK(CurrentDate),
        CALCULATE(
            SUM(Sales[Amount]),
            FILTER(
                ALLSELECTED(Dates[Date]),
                Dates[Date] <= CurrentDate
            )
        )
    )

LASTDATE returns the last date in the current filter context as a single-row table, which CALCULATE can use directly. The IF(NOT ISBLANK(...))) wrapper prevents the measure from returning a spurious zero on rows where no date resolves at all — which matters when your visual shows future dates that have no data yet.

Fix 4: Cumulative Totals Across Categories

Cumulative totals across a non-date dimension — cumulative product count, cumulative headcount by department — follow the same logic but need the category sorted consistently. Power BI evaluates the visual rows in display order, but DAX doesn't inherently know what "cumulative by category" means unless you tell it.

Assume you want cumulative sales by product category, sorted alphabetically:

Cumulative Sales by Category =
VAR CurrentCategory = MAX(Products[Category])
RETURN
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(
            ALLSELECTED(Products[Category]),
            Products[Category] <= CurrentCategory
        )
    )

The <= comparison works because Power BI sorts text alphabetically by default. If your category has a custom sort order defined, add a sort column (an integer rank) and compare on that instead:

Cumulative Sales by Category (Sorted) =
VAR CurrentRank = MAX(Products[CategoryRank])
RETURN
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(
            ALLSELECTED(Products[CategoryRank]),
            Products[CategoryRank] <= CurrentRank
        )
    )

This pattern is more reliable than alphabetical comparison, and it gives you explicit control over the accumulation order.

Relationship issues can also distort cumulative results when the filter propagates incorrectly across tables. If the numbers still look wrong after fixing the measure logic, check whether your model has relationships causing duplicate rows in visuals — that's a separate problem that multiplies values before the cumulative sum even runs.

Common Pitfalls That Sneak Past Testing

The measure works in a table but not a matrix

A matrix visual has both row and column filter contexts active simultaneously. If your cumulative measure only handles one axis, it may work correctly on rows but reset for each column. Extend your FILTER logic to cover whichever axis you're accumulating across, and verify in both a flat table and a matrix before releasing.

ALLSELECTED behaves unexpectedly inside a visual-level filter

ALLSELECTED respects filters from outside the visual (slicers, page filters) but removes filters from the visual itself. If you apply a visual-level filter to the date field directly in the Filters pane on the visual, ALLSELECTED will not respect it. Move that filter to the page or report level, or restructure the measure to use KEEPFILTERS if you need to honor visual-level filters too.

Totals row shows the grand total, not the last cumulative value

The totals row in a table visual evaluates the measure with all dates selected, not just the last one. MAX(Dates[Date]) returns the last date across the entire visible range, so the total row usually ends up showing the full sum — which is actually correct behavior for a cumulative total. If your totals row is showing something different (a blank or a partial sum), wrap the measure in a check: verify that HASONEVALUE(Dates[Date]) is false at the total level and return the full SUM directly in that case.

The date table isn't marked as a date table

If your date table isn't marked as a date table in Power BI Desktop (right-click the table in Model view → Mark as date table), DAX time intelligence functions behave inconsistently. LASTDATE, DATESYTD, and related functions rely on this metadata. Mark it explicitly and make sure the date column has no blanks and no duplicate dates.

Gaps and duplicates in dimension tables cause a whole class of silent errors in DAX — the same class of problems you'll find when SQL GROUP BY silently excludes NULLs, except in DAX the wrong answer often looks superficially plausible.

Cross-filter direction multiplies values

Bidirectional relationships can cause a fact table to be filtered from both sides, which duplicates rows before aggregation. A cumulative sum on top of duplicated rows will appear to accumulate correctly but will be inflated. Switch to single-direction relationships wherever possible, or use CROSSFILTER inside CALCULATE to control the direction explicitly for specific measures.

Wrapping Up: Next Steps

Cumulative total bugs in Power BI are almost always a filter context problem. The fix path is short once you know where to look: replace ALL with ALLSELECTED, anchor the date dynamically with MAX or LASTDATE, and verify your model relationships aren't inflating values before the measure runs.

Here are four concrete actions to take right now:

1. Audit your existing cumulative measures for ALL vs. ALLSELECTED. If users apply date slicers, you almost certainly want ALLSELECTED.
2. Test every cumulative measure in a matrix visual, not just a table. Matrices expose row-and-column context issues that flat tables hide.
3. Check your date table is marked as a date table in Model view and has a fully continuous, gap-free date column.
4. Add a totals-row test to your QA checklist. Verify the grand total row returns the expected value (usually the final cumulative value, or the full sum, depending on your spec).
5. Review your relationship directions in Model view. Any bidirectional relationship touching your fact table is a candidate for inflating aggregations.

If you're also seeing issues in other tools where aggregations silently produce wrong results, the pattern of thinking is similar — check what SQL window functions do when PARTITION BY is missing and you'll recognize the same "missing context" root cause across the stack.