Power BI Running Total & Cumulative Sum
Complete DAX guide to running totals, cumulative sums, WINDOW functions, YTD accumulations, and category resets with enterprise performance optimization.
Power BI Running Total: The Complete DAX Guide
How do you calculate a running total in Power BI? Use the DAX formula: Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))). This pattern uses CALCULATE to modify the filter context, ALL to remove existing date filters, and FILTER to include all dates up to and including the current row. For Power BI 2024+, the WINDOW function offers a cleaner alternative: CALCULATE(SUM(Sales[Amount]), WINDOW(1, ABS, 0, REL, ORDERBY(DateTable[Date]))).
Running totals and cumulative sums are among the most frequently requested calculations in Power BI enterprise deployments. Whether you are tracking cumulative revenue, progressive budget consumption, or year-to-date inventory levels, running totals transform point-in-time data into trend narratives that drive strategic decisions.
Yet running totals are also one of the most commonly misconfigured DAX patterns. Incorrect filter handling produces numbers that look right at the grand total but break at individual rows. Performance degrades silently as datasets grow, with some running total measures consuming 10x the resources of simple aggregations on billion-row datasets.
This guide covers every running total pattern available in Power BI as of 2026 — from the classic CALCULATE + FILTER approach to the modern WINDOW function, with performance benchmarks, common pitfalls, and enterprise optimization strategies used by EPC Group's Power BI consulting team across Fortune 500 deployments.
What is a Running Total in Power BI?
A running total (also called a cumulative sum) progressively adds values across an ordered sequence. Each row shows the sum of all preceding rows plus the current row.
Cumulative Revenue
Track total revenue accumulation over days, weeks, or months. Visualize how revenue builds throughout a fiscal period and compare against targets. Running totals reveal revenue velocity — whether you are front-loaded or back-loaded in hitting targets.
Budget Consumption
Show progressive spending against annual or quarterly budgets. A running total of expenses divided by the budget creates a real-time burn rate visualization. Finance teams use this to predict whether departments will come in under or over budget.
Pareto Analysis
Cumulative percentage of total enables 80/20 analysis — sorting products by revenue and showing cumulative contribution identifies the vital few that drive most results. This requires a running total divided by a grand total, formatted as a percentage.
YTD Performance
Year-to-date running totals reset at each January 1st (or fiscal year start), providing clean period comparisons. Compare this year YTD against prior year YTD to gauge performance trajectory without waiting for the period to close.
Inventory Tracking
Running totals of inventory receipts minus shipments produce current stock levels at any point in time. This is critical for supply chain visibility — each row represents the cumulative balance after all transactions up to that date.
Customer Lifetime Value
Cumulative purchase amounts per customer over time show CLV growth trajectories. Running totals partitioned by customer segment reveal which cohorts accumulate value fastest, informing retention and acquisition investment decisions.
Basic Running Total DAX Pattern
The foundational running total pattern uses CALCULATE with FILTER and ALL. Understanding this pattern is essential before exploring advanced variations.
Classic CALCULATE + FILTER Pattern
// Basic Running Total by Date
Running Total =
CALCULATE(
SUM( Sales[Amount] ),
FILTER(
ALL( DateTable[Date] ),
DateTable[Date] <= MAX( DateTable[Date] )
)
)
How It Works:
- CALCULATE modifies the filter context for SUM
- ALL(DateTable[Date]) removes existing date filters
- FILTER keeps dates <= current row date
- MAX(DateTable[Date]) captures the current date context
- Result: sum of all values from start up to current row
WINDOW Function (2024+)
// Modern Running Total with WINDOW
Running Total =
CALCULATE(
SUM( Sales[Amount] ),
WINDOW(
1, ABS, // from row 1 (absolute)
0, REL, // to current row (relative)
ORDERBY( DateTable[Date] )
)
)
Why Use WINDOW:
- Cleaner syntax — no nested FILTER + ALL
- Engine-optimized for windowed calculations
- Native PARTITIONBY for category resets
- Supports OFFSET and INDEX for lag/lead patterns
- Consistent with SQL window function mental model
Running Total by Date: Daily, Monthly, Quarterly
Running totals at different date granularities require adjusted DAX patterns. The date level in your visual determines which pattern you need.
Daily Running Total
Running Total Daily = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date])))
Use when your visual shows individual dates. The most granular level — accumulates day by day. Requires a date table with no gaps.
Monthly Running Total
Running Total Monthly = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[YearMonth]), DateTable[YearMonth] <= MAX(DateTable[YearMonth])))
Use when your visual shows months. Replace the date column with a YearMonth column (formatted as YYYYMM integer for correct sorting). Alternatively, use FILTER with YEAR and MONTH functions.
Quarterly Running Total
Running Total Quarterly = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[YearQuarter]), DateTable[YearQuarter] <= MAX(DateTable[YearQuarter])))
Use when your visual shows quarters. Requires a YearQuarter column (e.g., 2026Q1 formatted as integer 20261 for sort). Each quarter row shows the cumulative sum of all quarters up to and including the current one.
Running Total by Category (Partitioned Reset)
Enterprise reports frequently need running totals that restart for each product line, region, or business unit. Two approaches handle this.
CALCULATE + VALUES Pattern
// Running total that resets per category
RT by Category =
CALCULATE(
SUM( Sales[Amount] ),
FILTER(
ALL( DateTable[Date] ),
DateTable[Date] <= MAX( DateTable[Date] )
),
VALUES( Products[Category] )
)
VALUES(Products[Category]) preserves the current category filter context. The running total accumulates only within the current category. Works in all Power BI versions.
WINDOW + PARTITIONBY Pattern
// Modern partitioned running total
RT by Category =
CALCULATE(
SUM( Sales[Amount] ),
WINDOW(
1, ABS,
0, REL,
ORDERBY( DateTable[Date] ),
PARTITIONBY( Products[Category] )
)
)
PARTITIONBY explicitly defines the reset boundary. Cleaner syntax and often better performance on large datasets. Requires Power BI December 2023 or later.
SUMX Running Total Pattern
When your running total requires row-level calculations before accumulation, SUMX provides the flexibility to compute per-row values within the running window.
// Running total of calculated margin (row-level computation)
Running Margin =
CALCULATE(
SUMX(
Sales,
Sales[Revenue] - Sales[Cost]
),
FILTER(
ALL( DateTable[Date] ),
DateTable[Date] <= MAX( DateTable[Date] )
)
)
// Running total of weighted average price
Running Weighted Avg =
VAR RunningRevenue = CALCULATE(
SUM( Sales[Revenue] ),
FILTER( ALL( DateTable[Date] ), DateTable[Date] <= MAX( DateTable[Date] ) )
)
VAR RunningQty = CALCULATE(
SUM( Sales[Quantity] ),
FILTER( ALL( DateTable[Date] ), DateTable[Date] <= MAX( DateTable[Date] ) )
)
RETURN DIVIDE( RunningRevenue, RunningQty )
When to Use SUMX:
- Row-level margin or profit calculations
- Running total of computed expressions (not stored columns)
- Weighted running averages requiring per-row weights
- Cumulative distinct counts with row logic
Performance Caution:
- SUMX iterates every row in the running window
- Window grows with each date — later dates scan more rows
- On billion-row tables, this can be 100x slower than SUM
- Pre-aggregate computed values in Power Query when possible
Cumulative Percentage of Total
Running total as a percentage reveals progress toward targets and enables Pareto analysis. This pattern divides a running total by the grand total.
// Cumulative percentage of total
Cumulative % =
VAR RunningTotal =
CALCULATE(
SUM( Sales[Amount] ),
FILTER(
ALL( DateTable[Date] ),
DateTable[Date] <= MAX( DateTable[Date] )
)
)
VAR GrandTotal =
CALCULATE( SUM( Sales[Amount] ), ALL( DateTable[Date] ) )
RETURN
DIVIDE( RunningTotal, GrandTotal )
Pareto Analysis
Sort products by revenue descending and apply cumulative %. The point where the line crosses 80% shows your vital few products. Typically 15-20% of products drive 80% of revenue.
Budget Tracking
Running expenses as a % of annual budget shows burn rate visually. If you are at 60% spent but only 40% through the year, the S-curve reveals the overspend trajectory early.
Goal Progress
Cumulative sales as a % of annual target creates a goal tracker. Compare against a linear benchmark line (equal daily contribution) to see if you are ahead or behind pace.
YTD Running Total (Resets by Year)
Year-to-date running totals automatically reset at each year boundary. Power BI provides built-in functions and manual patterns for both calendar and fiscal years.
TOTALYTD (Simplest)
YTD Sales = TOTALYTD(SUM(Sales[Amount]), DateTable[Date])
The built-in approach. Automatically resets at January 1st. For fiscal year ending June 30: TOTALYTD(SUM(Sales[Amount]), DateTable[Date], "6/30").
DATESYTD (More Control)
YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD(DateTable[Date]))
Returns the set of dates from January 1 to the current date in the filter context. Wrapping in CALCULATE lets you add additional filters. For fiscal year: DATESYTD(DateTable[Date], "6/30").
Manual YTD (Maximum Flexibility)
YTD Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable), DateTable[Year] = MAX(DateTable[Year]) && DateTable[Date] <= MAX(DateTable[Date])))
Full manual control. Filter to the current year AND dates up to the current date. Allows custom fiscal year logic, multi-year comparisons, and complex conditional resets that the built-in functions cannot handle.
Resetting Running Total by Period
Beyond YTD, enterprise reports need running totals that reset by quarter, month, week, or custom fiscal periods.
Reset by Quarter
QTD Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable), DateTable[YearQuarter] = MAX(DateTable[YearQuarter]) && DateTable[Date] <= MAX(DateTable[Date])))
Filter to the current quarter and accumulate within it. Requires a YearQuarter column in your date table.
Reset by Month
MTD Running Total = CALCULATE(SUM(Sales[Amount]), DATESMTD(DateTable[Date]))
DATESMTD provides month-to-date accumulation. Resets on the 1st of each month. For manual control, filter on Year+Month.
Reset by Fiscal Period
Fiscal Period RT = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable), DateTable[FiscalPeriod] = MAX(DateTable[FiscalPeriod]) && DateTable[Date] <= MAX(DateTable[Date])))
Custom fiscal periods (4-4-5, 4-5-4, etc.) require a FiscalPeriod column in the date table. The running total resets at each period boundary.
Reset by WINDOW PARTITIONBY
Period RT = CALCULATE(SUM(Sales[Amount]), WINDOW(1, ABS, 0, REL, ORDERBY(DateTable[Date]), PARTITIONBY(DateTable[YearQuarter])))
PARTITIONBY creates natural reset boundaries. Specify any column — quarter, month, fiscal period, or custom grouping. The cleanest syntax for period resets.
Performance Comparison: Running Total Patterns
Running total performance varies dramatically based on the pattern used and the dataset size. These benchmarks are from EPC Group testing on enterprise datasets.
| Pattern | 1M Rows | 100M Rows | 1B+ Rows | Compatibility |
|---|---|---|---|---|
| CALCULATE + FILTER + ALL | < 1s | 2-5s | 10-30s | All versions |
| WINDOW function | < 1s | 1-3s | 5-15s | Dec 2023+ |
| TOTALYTD / DATESYTD | < 1s | 1-2s | 3-8s | All versions |
| SUMX + FILTER + ALL | 1-3s | 10-30s | 60-180s | All versions |
| Calculated Column | Refresh | Refresh | Refresh | All versions |
EPC Group recommendation: Use WINDOW for new development on Power BI 2024+. Use TOTALYTD/DATESYTD for year-to-date patterns. Fall back to CALCULATE+FILTER+ALL for maximum compatibility. Avoid SUMX patterns on large datasets unless row-level computation is truly required.
Common Running Total Pitfalls
Missing ALL() on the date column
CriticalWithout ALL, the running total only shows the current row value — it does not accumulate. Always use ALL(DateTable[Date]) or ALL(DateTable) to remove the date filter before applying your custom range.
Date table has gaps
HighIf your date table skips weekends or holidays, running totals may show unexpected jumps. Ensure your date table is contiguous — every date from start to end, regardless of whether transactions occurred.
Wrong date column reference
HighUsing the fact table date column instead of the date table date column causes incorrect accumulation. Always reference DateTable[Date] in your FILTER expression, not Sales[OrderDate].
ALLSELECTED instead of ALL
MediumALLSELECTED respects slicer selections, which means your running total only accumulates within the sliced range. Use ALL for true cumulative totals. Use ALLSELECTED only when you intentionally want slicer-bounded accumulation.
Grand total shows wrong value
MediumThe running total grand total often shows a doubled or tripled value because the grand total row has MAX(Date) = last date, and the accumulation sums everything. Use IF(HASONEVALUE(DateTable[Date]), [Running Total], [Simple Total]) to display the correct grand total.
Visual-level filters breaking accumulation
MediumVisual filters applied directly on the visual can interfere with ALL() and produce partial accumulations. Move filters to the measure logic using CALCULATE instead of relying on visual-level filters.
Best Visuals for Running Totals
Choosing the right visual type for running total data maximizes insight clarity and stakeholder comprehension.
Area Chart
The most common choice for running totals. The filled area emphasizes the cumulative growth visually. Use for single-series running totals like YTD revenue. Add a target line for goal tracking.
Line + Clustered Column
Combine individual period values (columns) with the running total (line). Stakeholders see both the per-period contribution and the cumulative trajectory in one visual.
KPI Card + Sparkline
For executive dashboards, show the current running total as a large KPI number with a sparkline showing the accumulation trend. Pair with conditional formatting for target comparison.
Waterfall Chart
Waterfall charts naturally show cumulative progression. Each bar represents the period contribution, with a running subtotal line. Ideal for budget consumption and P&L running totals.
Multi-Series Line
Compare running totals across categories, regions, or years. Each line represents a separate running total. Use for YoY comparison (this year YTD vs prior year YTD).
Table with Data Bars
For detail-oriented stakeholders, a table showing individual values alongside running totals with conditional data bars provides both precision and visual context.
Need Expert Power BI DAX Development?
EPC Group's Power BI team has built running total solutions across Fortune 500 organizations — from simple YTD accumulations to complex multi-dimensional cumulative models on billion-row datasets. We deliver production-grade DAX that performs at scale.
Frequently Asked Questions: Power BI Running Totals
How do you calculate a running total in Power BI?
The most common running total pattern in Power BI uses CALCULATE with FILTER and a date comparison: Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))). This sums all rows where the date is less than or equal to the current date context. The ALL function removes existing date filters so the running total accumulates from the earliest date. For best performance on large datasets, use the WINDOW function introduced in 2024: Running Total = CALCULATE(SUM(Sales[Amount]), WINDOW(1, ABS, 0, REL, ORDERBY(DateTable[Date]))).
What is the difference between a running total and a cumulative sum in Power BI?
In Power BI, running total and cumulative sum are functionally identical concepts — both refer to a progressive accumulation of values across an ordered sequence (typically dates). The term "running total" is more commonly used in business reporting, while "cumulative sum" is more common in statistical and analytical contexts. Both are implemented with the same DAX patterns. The only distinction some analysts make is that "running total" implies a strictly additive accumulation, while "cumulative sum" can include negative values (like net cumulative profit/loss). The DAX implementation is the same for both.
How do you create a running total by category in Power BI?
To create a running total that resets by category, you need to include the category in your FILTER expression. Pattern: Running Total by Category = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date])), VALUES(Products[Category])). The VALUES(Products[Category]) preserves the current category filter context while ALL(DateTable[Date]) removes only the date filter. This creates separate running totals per category. With the WINDOW function, use PARTITIONBY: CALCULATE(SUM(Sales[Amount]), WINDOW(1, ABS, 0, REL, ORDERBY(DateTable[Date]), PARTITIONBY(Products[Category]))).
What is the WINDOW function for running totals in Power BI?
The WINDOW function, introduced in Power BI in late 2023/2024, provides a native way to calculate running totals without complex CALCULATE+FILTER patterns. Syntax: WINDOW(from, from_type, to, to_type, ORDERBY(column), PARTITIONBY(column)). For a running total: WINDOW(1, ABS, 0, REL) means "from absolute row 1 to the current relative row 0." Combined with ORDERBY for sort order and optional PARTITIONBY for resets, this is cleaner and often faster than the traditional CALCULATE pattern. WINDOW also supports OFFSET and INDEX for advanced windowing scenarios like moving averages and lag/lead calculations.
How do you calculate a YTD running total in Power BI?
For a year-to-date running total, you have two approaches. First, use the built-in TOTALYTD function: YTD Running Total = TOTALYTD(SUM(Sales[Amount]), DateTable[Date]). This automatically resets at each year boundary. Second, for more control, use CALCULATE with DATESYTD: YTD Running Total = CALCULATE(SUM(Sales[Amount]), DATESYTD(DateTable[Date])). For fiscal year support, add the fiscal year end date as a second parameter: TOTALYTD(SUM(Sales[Amount]), DateTable[Date], "6/30"). Both approaches require a properly configured date table marked as a date table in your Power BI model.
Why is my Power BI running total showing incorrect values?
The most common causes of incorrect running totals in Power BI are: 1) Missing ALL() function — without ALL on the date column, the running total only considers the current filter context instead of accumulating, 2) Date table issues — gaps in dates, missing date table relationship, or date table not marked as date table, 3) Wrong granularity — using Date when your data is monthly or vice versa, 4) Filter context interference — report filters, page filters, or slicers affecting the accumulation, 5) Multiple fact tables — ambiguous relationships preventing correct filter propagation, 6) Using ALLSELECTED instead of ALL — ALLSELECTED respects slicer selections which can break accumulation logic. Debug by testing your measure in a simple table visual with just dates before adding complexity.
How does the running total CALCULATE pattern work in DAX?
The classic running total DAX pattern has three components: Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))). Here is what each part does: 1) CALCULATE changes the filter context for the inner expression, 2) SUM(Sales[Amount]) is the value being accumulated, 3) FILTER(ALL(DateTable[Date]), ...) creates a new filter — ALL removes existing date filters and FILTER keeps only dates up to and including the current row, 4) MAX(DateTable[Date]) captures the current date context from the visual. The result: for each row in a visual, the measure sums all values from the beginning up to that row date.
Can you create a running total percentage in Power BI?
Yes. A cumulative percentage of total shows each row contribution as a running percentage. Pattern: Cumulative % = VAR RunningTotal = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))) VAR GrandTotal = CALCULATE(SUM(Sales[Amount]), ALL(DateTable[Date])) RETURN DIVIDE(RunningTotal, GrandTotal). This creates an S-curve visualization showing what percentage of the total has been accumulated at each point. Format the measure as percentage. This is especially useful for Pareto analysis (80/20 rule), budget consumption tracking, and project milestone progress reporting.
How do you optimize running total performance in Power BI?
Running totals can be slow on large datasets because the FILTER+ALL pattern scans increasingly more rows as the date progresses. Optimization strategies: 1) Use WINDOW function (2024+) instead of CALCULATE+FILTER — internally optimized by the engine, 2) Pre-aggregate in Power Query — if you only need daily running totals, aggregate to daily level before DAX, 3) Use ALLSELECTED instead of ALL when full accumulation is not needed, 4) Limit date range with DATESBETWEEN to cap the accumulation window, 5) Use VAR to store intermediate calculations once, 6) Consider a calculated column for static running totals that do not change with filters, 7) Implement incremental refresh to keep the dataset manageable. EPC Group has optimized running total calculations on datasets exceeding 2 billion rows.
How does EPC Group implement running totals for enterprise clients?
EPC Group follows a structured approach for enterprise running total implementations: 1) Requirement analysis — determine if the running total needs to reset by period, category, or other dimension, 2) Date table validation — ensure the date table is continuous, properly marked, and has the right granularity, 3) Pattern selection — choose between CALCULATE+FILTER (compatibility), WINDOW (performance), or TOTALYTD (simplicity) based on the Power BI version and complexity, 4) Performance testing — validate with production data volumes using DAX Studio, 5) Visual optimization — pair running totals with area charts or combo charts for clear visualization, 6) Documentation — every running total measure includes business definition and reset logic description. Our Power BI consultants have implemented running total solutions across financial services, healthcare, and manufacturing with datasets from 1 million to 5 billion rows.