The pattern in one line
CALCULATE( SUM(Sales[Amount]), FILTER( ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) ). The ALL() removes the row’s date filter; the FILTER re-adds every date up to the current one. That is the whole trick.
Power BI How-To / DAX Patterns
How to Create a Running Total in Power BI Using DAX
A power bi running total DAX measure adds each period’s value to the sum of every period before it, producing a cumulative line that grows across your date axis. This guide gives you the classic CALCULATE + FILTER pattern, a worked cumulative-sales example, YTD variants, the modern WINDOW function, and a troubleshooting checklist.
The short version
Six things that decide whether your cumulative measure works
You almost always need a date table
Running totals depend on a continuous, marked Date table related to your fact table. Calendars with gaps, text dates, or no ‘Mark as Date Table’ setting are the single most common reason cumulative measures break.
WINDOW is the modern way
DAX’s 2023-era WINDOW function computes running totals over a visual’s rows without the CALCULATE/FILTER boilerplate. It is cleaner for many cases but still rewards understanding the classic pattern underneath.
Reset per category on purpose
A cumulative total that should restart for each product or region needs ALLEXCEPT (or REMOVEFILTERS keeping the group column) so the filter context resets per group instead of summing across all of them.
YTD without writing FILTER
DATESYTD and TOTALYTD give you year-to-date running totals in one expression and respect a custom fiscal year-end. Use them when ‘cumulative’ means ‘since January 1’ rather than ‘since the beginning of time.’
When the fix is a model fix
If the measure is right but the report still crawls, the problem is usually the model — bad relationships, huge cardinality, or iterator-heavy DAX. That is the kind of tuning our Flex Support hours are built for.
What is a running total in Power BI, and when do you need one?
A running total (cumulative sum) adds the current period’s value to the sum of all prior periods, so the number climbs over time. You need one for cumulative sales-to-date, budget burn-down, pipeline build-up, headcount, or any ‘how much so far’ metric. DAX (Data Analysis Expressions) is Power BI’s formula language for building these measures.
Excel users reach for a running total constantly — drag a formula like =B2+C1 down a column and you have one. Power BI works differently: there is no row-above to reference, because measures are evaluated inside a filter context, not cell by cell. Instead you write one measure that, for whatever date is on the current row, sums every fact at or before that date. The same measure then works on a daily, monthly, or quarterly axis with no rewrite. That portability is the payoff for learning the pattern, and it is why running totals are one of the first ‘real DAX’ problems Excel-to-Power BI movers hit. If the language itself is new to you, it helps to first understand what DAX is (measures vs calculated columns) before wiring up cumulative logic.
What is the classic CALCULATE + FILTER running total DAX pattern?
The standard cumulative measure is CALCULATE( [Total Sales], FILTER( ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) ). CALCULATE changes filter context; ALL strips the current date filter so you can see all dates; FILTER keeps only dates up to and including the one on the current row. The result accumulates as the axis advances.
Read it inside-out. MAX('Date'[Date]) returns the last (current) date in the row’s context. ALL('Date'[Date]) clears the existing date filter so FILTER can scan the full calendar. FILTER then returns the set of dates less than or equal to the current one, and CALCULATE re-applies that set as the new filter for [Total Sales]. A common refinement is to use a base measure (Total Sales = SUM(Sales[Amount])) rather than inlining SUM, so the logic is reusable and easier to debug. Write the measure once, drop it on a line chart with Date on the axis, and you get a smooth cumulative curve.
Can you show a worked cumulative sales example with results?
Yes. Given daily sales of 100, 150, 0, and 300 across four dates, the running total measure returns 100, 250, 250, and 550 respectively — each day is its own sales plus everything banked before it. The results table below shows the daily figure beside the cumulative figure so you can verify the measure is accumulating correctly.
| Date | Daily Sales [Total Sales] | Running Total [Cumulative Sales] | What the measure did |
|---|---|---|---|
| 2026-01-01 | 100 | 100 | First date: cumulative equals the day’s sales |
| 2026-01-02 | 150 | 250 | Adds 150 to the prior 100 |
| 2026-01-03 | 0 | 250 | No sales; holds the banked balance flat |
| 2026-01-04 | 300 | 550 | Adds 300 to the running 250 |
Build it in three steps. First, a base measure: Total Sales = SUM(Sales[Amount]). Second, the cumulative measure: Cumulative Sales = CALCULATE( [Total Sales], FILTER( ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) ). Third, put 'Date'[Date] on a table or line visual with both measures. Note how the day with zero sales (the third date) holds the cumulative flat at 250 rather than dropping — proof the running total is carrying the prior balance, not recomputing from scratch. If your numbers reset to the daily value instead of accumulating, the ALL() is missing or your date column is not coming from a proper Date table. Microsoft’s CALCULATE function reference (opens in new tab) documents how it overrides the filter context that running totals depend on.
Why is my running total wrong — what is the troubleshooting checklist?
Wrong running totals almost always trace to one of five causes: no marked Date table, a date column with gaps or text values, the ALL() being omitted (totals reset instead of accumulating), the wrong granularity on the axis, or an interfering slicer/filter that ALL() does not clear. Work the list top to bottom.
Walk this checklist in order and the bug is usually obvious by step three:
- Is there a dedicated Date table, related to the fact table, with ‘Mark as Date Table’ set?
- Is the calendar continuous with no missing days?
- Are you filtering on the Date table’s date column, not the fact table’s?
- Does the measure use
ALL()(orREMOVEFILTERS) on the date so prior context is cleared? - Is the visual at the granularity you expect — daily vs. monthly changes what
MAX()returns? - Is another slicer (region, product) silently scoping the total in a way you did not intend?
- For category-level resets, did you switch
ALL()toALLEXCEPT()keeping the group column?
The order matters because the causes are roughly ranked by frequency. A missing or unmarked Date table breaks more cumulative measures than every other cause combined, so confirming the model before you touch the formula saves the most time.
How do you build a YTD or per-category running total?
For year-to-date, use TOTALYTD( [Total Sales], 'Date'[Date] ) or CALCULATE with DATESYTD — both reset on January 1 (or a fiscal year-end you specify). For a total that restarts per product or region, replace ALL('Date'[Date]) with ALLEXCEPT('Date', plus the grouping column) so the cumulative sum resets within each group.
YTD is the most-requested variant because finance and budget reporting live on it: TOTALYTD( [Total Sales], 'Date'[Date], "6/30" ) gives a fiscal year ending June 30. This is exactly the pattern behind a year-to-date actuals column when you build a budget vs actual report. For category resets, the FILTER pattern becomes CALCULATE( [Total Sales], FILTER( ALLEXCEPT( Sales, Sales[Product] ), 'Date'[Date] <= MAX('Date'[Date]) ) ), so each product accumulates independently. The newer WINDOW function (added to DAX in 2023) can express running totals as WINDOW( 1, ABS, 0, REL, ... ) over a visual’s rows and often reads cleaner, though it rewards already understanding the classic pattern. Microsoft’s DAX function reference (opens in new tab) documents the current syntax and version support for DATESYTD, TOTALYTD, and WINDOW.
When should you hand the running total — or the whole model — to a senior engineer?
If you have tried the pattern above and the total is still wrong, slow, or right in a table but broken in a matrix, the issue is usually upstream: model relationships, date-table setup, cardinality, or filter-context interactions that are hard to see from the formula alone. That is when a focused block of senior hours pays for itself.
Power BI Flex Support is prepaid, senior-led Power BI hours that never expire — no contracts, no project minimums. You buy a block (STARTER 20h @ $125/hr = $2,500; GROWTH 40h @ $115/hr = $4,600; POWER 80h @ $100/hr = $8,000, best value), email a request, and we send a scope-and-hours estimate before any work; hours are deducted only for approved work, most tasks land 3–5 business days after approval, and there is a full refund if you are unhappy within the first 4 hours of a block. Typical firms charge $175–250/hr with $10K+ minimums and a 4–6 week start; we are US-based, senior, and can start this week. A single running-total bug is often a 1–2 hour fix — and we will tell you that up front. When the real problem is upstream, the same hours cover data model and DAX tuning, and you can get DAX help with Flex hours without signing a contract. Contact is through the form on this site.
Stuck on a running total that won’t add up?
Most cumulative-measure bugs are a one-to-two-hour fix, and a tangled model is the usual culprit. Bring yours to a free demo — we scope it, you approve, and prepaid hours that never expire cover the work. Most fixes land in 3–5 business days.
FAQ
Frequently asked questions
ALL('Date'[Date]) removes the date filter coming from the current row or visual, letting FILTER scan the entire calendar. Without it, the measure only ever sees the single current date, so the total resets to that day’s value instead of accumulating. Omitting ALL() is the number-one reason running totals ‘don’t add up.’
Almost always, yes. Running total functions like DATESYTD and the FILTER/ALL pattern assume a continuous, marked Date table related to your fact table. Using dates straight from the fact table — especially with gaps or text-formatted dates — produces missing or incorrect cumulative values. Build a Date table, mark it, and relate it once.
WINDOW (added to DAX in 2023) is cleaner for running totals computed over a visual’s rows and is worth adopting for new reports. The classic CALCULATE + FILTER + ALL pattern is still the most widely supported and the best one to learn first, because it teaches the filter-context thinking every other pattern relies on.
Swap ALL() for the right scope. For a monthly reset, restrict FILTER to dates in the same month; for a per-category reset, use ALLEXCEPT keeping the grouping column (e.g. Product) so each group accumulates on its own. For year-to-date that resets every January, TOTALYTD or DATESYTD handles it in one line.
A correct-but-slow running total usually points to the model, not the formula: high-cardinality columns, iterator-heavy DAX, or weak relationships. That is a model-tuning job. A prepaid Flex Support block (senior-led, hours never expire) is built for exactly this — we scope it, quote the hours before working, and most fixes land in 3-5 business days. Reach us through the site form.
Try us risk-free for 4 hours
If you’re unhappy within the first 4 hours of your block, we’ll refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, and they never expire.
Book a free demo
Get senior Power BI help, on demand
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
- Prepaid hours from $100/hour — and they never expire
- Senior-led delivery — 20+ years on the Microsoft platform
- Typical 3–5 business day turnaround
- Full refund if you’re unhappy in your first 4 hours
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.