How to Calculate Cumulative Total in Power BI

Calculating a running, or cumulative, total is one of the most fundamental skills you'll need in Power BI. Whether you're tracking year-to-date sales, month-over-month user growth, or campaign spending over time, this calculation turns a simple list of numbers into a powerful trendline. This guide will walk you through exactly how to create a cumulative total using DAX, starting with the basic formula and then covering more advanced, real-world scenarios.

Why Running Totals are So Important

Before diving into the formulas, it's helpful to understand why cumulative totals matter. Standard metrics show you performance in isolated moments - how many sales did we get on Tuesday? How many users signed up in March? A running total provides the narrative context.

Think about these common business questions:

• Sales Performance: "Are we on track to hit our quarterly sales target?" A running total of sales revenue makes the trajectory instantly clear.
• Marketing Campaigns: "How much have we spent on our Q3 campaign so far?" A simple SUM shows the total, but a cumulative total shows the spending pace over the campaign's lifetime.
• Project Management: "How many hours have we logged against the project budget to date?" Tracking cumulative hours helps you see if you're burning through your budget too quickly.

In all these cases, a cumulative total helps you understand momentum and progress over time, which is far more valuable than a static snapshot.

Understanding the Core of Power BI Calculations: DAX and the CALCULATE Function

To create a running total, you need to use DAX (Data Analysis Expressions), which is the formula language for Power BI. While DAX can seem intimidating, it's built around a few core building blocks. For our purposes, the most important one is the CALCULATE function.

Simply put, CALCULATE is the most powerful function in DAX. It lets you change the "context" in which a calculation happens. Think of it like this:

• The first part of CALCULATE is the expression you want to evaluate (e.g., SUM([Sales])).
• The following parts are the filters you want to apply that modify the context (e.g., for a specific time period or product category).

This ability to dynamically change the filter context is exactly what we need to build a running total.

Method 1: The Classic Cumulative Total Formula (Step-by-Step)

Let's create a cumulative total for sales. This is an all-purpose running total that will add up sales values from the beginning of your dataset to the "current" date in your report visualization (like a table row or a point on a line chart).

Step 1: Get Your Data Ready

First, make sure you have a sales table with at least two columns: a date column (e.g., OrderDate) and a value column (e.g., SalesAmount). For best results, you should also have a dedicated 'Date' table in your model, which we'll cover in a moment.

Step 2: Create a New Measure

In the Report view in Power BI, right-click on your sales table in the 'Data' pane and select "New Measure." This will open the formula bar.

Step 3: Write the DAX Formula

Enter the following DAX formula into the formula bar. We'll break down what each part does below.

Cumulative Sales =
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Let’s Break Down That Formula:

• Cumulative Sales =: This is simply the name we're giving our new measure.
• CALCULATE( SUM(Sales[SalesAmount]), ... ): We're telling DAX to calculate the total sum of the SalesAmount column, but we're going to apply a special filter to change its default behavior.
• FILTER( ... ): This function creates the new filtering context for our calculation. It works on a table and applies a filter condition.
• ALL('Date'[Date]): This is the magic key. The ALL function removes any existing filters from the 'Date' table's 'Date' column. Why? Because when you put this measure in a table or on a chart, each row/point has an inherent date context (e.g., "January 5th"). To calculate a running total, we need to temporarily ignore that single-day context so we can look at all dates.
• 'Date'[Date] <= MAX('Date'[Date]): This is our filter condition. For every date in our temporarily unfiltered date table, it checks if that date is less than or equal to the "maximum date" in the current context. The MAX('Date'[Date]) part returns the date of the current row in your visual. In effect, it says: "Add up all sales for the dates that occurred on or before the current date in the visual."

When you use this measure in a visual (like a table with 'Date' and 'SalesAmount'), you'll see a third column, "Cumulative Sales," that grows with each passing day.

Heads Up: You Need a Proper Date Table

DAX time intelligence functions—and this running total pattern—work best when you have a dedicated 'Date' table in your data model.

A date table is a simple table that has one row for every single day in a given period (e.g., from January 1, 2022, to December 31, 2024), without any gaps. It should also have columns for Year, Quarter, Month, etc. You can create one easily in Power Query or using DAX's CALENDAR() or CALENDARAUTO() functions.

Once you've created it:

1. Create a relationship between your date table's date column and the date column in your sales table (the ALL('Date'[Date]) part of our formula refers to this table).
2. Right-click your date table and select "Mark as Date Table," then choose your primary date column.

This small setup makes your DAX formulas simpler, more robust, and faster.

Method 2: Handling Totals That Reset Each Year (YTD, MTD, QTD)

The classic formula above will keep accumulating forever. But often, you want a Year-to-Date (YTD) total that resets back to zero every January 1st.

Thankfully, DAX has built-in time intelligence functions that make this incredibly easy. If you have a proper date table, calculating YTD sales is much simpler:

Sales YTD = TOTALYTD(SUM(Sales[SalesAmount]), 'Date'[Date])

Breaking Down the YTD Formula:

• TOTALYTD(...): This is a specialized, pre-built function for calculating year-to-date totals.
• SUM(Sales[SalesAmount]): This is our expression — what we want to calculate.
• 'Date'[Date]: This tells the function which date column to use for its calculation (it must come from your properly marked Date Table).

Similar functions exist for Month-to-Date (TOTALMTD) and Quarter-to-Date (TOTALQTD), saving you the hassle of writing more complex FILTER logic.

Advanced Topic: Running Totals Across Different Categories

What if you want to see a running total, but have it filtered by a slicer for product categories? For instance, you want to see the cumulative sales for "Laptops" and a separate one for "Monitors."

The good news is that the "classic" formula we created in Method 1 already handles this! Because it only removes the filter on the date column (ALL('Date'[Date])), any other filters from slicers or other visuals (like product category) remain active.

However, if you want to create a more explicit formula to calculate a cumulative total for each category but ignore date context, you can use ALLEXCEPT.

Cumulative Sales by Category =
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        ALLEXCEPT(Sales, Sales[ProductCategory]),
        Sales[OrderDate] <= MAX(Sales[OrderDate])
    )
)

Here, ALLEXCEPT(Sales, Sales[ProductCategory]) removes all filters from the 'Sales' table except for the one on 'ProductCategory'. This pattern can be useful in more complex scenarios where you need precise control over which filters are applied.

Troubleshooting Common Problems

• My total is the same on every single row. You likely forgot the second part of the FILTER condition: 'Date'[Date] <= MAX('Date'[Date]). Without it, ALL just removes the date filter and sums everything for every row.
• My total is wrong when I use MTD/YTD functions. This is almost always because you don't have a properly configured date table, or it’s not marked as the official date table for the dataset.
• My running total has gaps. This can happen if your date table is missing dates. Ensure it is a continuous, unbroken list of days covering your entire analysis period.

Learning this running total pattern opens the door to much more powerful analysis in Power BI, allowing you to tell compelling stories with your data by showing trends, not just moments.

Final Thoughts

Mastering the cumulative total in Power BI boils down to understanding how CALCULATE, combined with functions like FILTER and ALL, can manipulate the context of your data to look beyond a single point in time. Whether you use the classic pattern for a perpetual running total or time intelligence functions like TOTALYTD for resetting totals, you're now equipped to add valuable narrative depth to any report.

While learning DAX is rewarding, we know it involves a steep learning curve that keeps many teams stuck manually exporting CSVs into spreadsheets. For professionals who need answers fast without becoming DAX experts, our platform, Graphed, helps you skip the formulas entirely. We allow you to connect your data sources from platforms like Google Analytics or your CRM, then use plain English to build real-time dashboards and reports. Instead of learning functions and debugging formulas, you can simply ask, "show me cumulative sales year to date as a line chart," and get the visualization you need in seconds.