How to Calculate Running Total in Power BI DAX

A running total, or cumulative sum, is one of the most powerful calculations in data analysis, allowing you to see how a value accumulates over time. If you’ve ever tried to create one in Power BI, you’ve likely stumbled into the world of DAX. This article will break down exactly how to calculate a running total using DAX in a clear, step-by-step way.

What is a Running Total, and Why is it Useful?

A running total is a calculation that sums a value sequentially over a dimension, typically time. Instead of just seeing sales for January and sales for February, a running total shows you January's sales, then January + February's sales, and so on. It provides a clear view of performance trends and growth trajectories.

Here are a few practical examples where running totals are essential:

• Sales Reporting: Tracking cumulative revenue throughout the year to see if you're on pace to hit your annual target.
• Inventory Management: Monitoring the total stock received over time to understand inflow trends.
• Marketing Analytics: Visualizing the total number of new subscribers or website users acquired since the start of a campaign.
• Project Management: Summing the total hours logged against a project week after week to track budget consumption.

In all these cases, the running total tells a story that isolated data points can't. It shows momentum and provides context for the current period's performance.

The Key DAX Functions for Calculating Running Totals

To build a running total in Power BI, you need to get comfortable with a few core DAX (Data Analysis Expressions) functions. The main pattern combines three key players:

• CALCULATE(): This is the most important function in DAX. Its job is to modify the "context" in which a calculation runs. For a running total, we use it to tell our SUM function to ignore its default filters (like a single month) and instead look at a different set of data (like all months up to the current one).
• SUM(): A simple but essential aggregation function. It does the basic work of summing up the numbers in a column, such as Sales[Revenue] or Inventory[Units].
• FILTER() and ALL(): These functions work together to define the new context for CALCULATE. ALL() strips away any pre-existing filters from a column (like the specific date on a given row of a table), giving us the entire column to work with. Then, FILTER() applies a new, custom rule to that unfiltered set of data.

Combined, these functions allow us to create a measure that says: "For any given point in time (like March 31st), calculate the sum of my value for all dates up to and including that point."

Step-by-Step: Creating a Running Total in Power BI

Let's walk through building a running total for sales. For this to work best, we need two things: a solid data model and a base measure.

Before You Start: Prerequisite for All Time Intelligence

The most important rule in Power BI time intelligence is to have a dedicated Date Table. This is a separate table containing a continuous list of dates, along with columns for year, quarter, month, etc. You should connect this date table's date column to the date column in your fact table (like your sales table).

Why? A dedicated date table provides a stable, complete timeline for DAX to work with, which prevents errors and ensures your time-based calculations are accurate, even if you have gaps in your sales data.

Step 1: Create a Base Measure

First, always create a basic measure for the value you want to total. This makes your formulas cleaner and more reusable. On the Home or Modeling tab in Power BI, click "New Measure."

Total Sales = SUM(Sales[SalesAmount])

This simple measure gives us a foundation to build on.

Step 2: Create the Running Total Measure

Now, let's create the measure for our running total. Click "New Measure" again and enter the following DAX formula. This is the classic, most explicit pattern, which is great for learning.

Running Total Sales = 
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Let's break down what this is doing piece by piece:

• CALCULATE([Total Sales], ...): We are telling Power BI to calculate our base [Total Sales] measure, but we're going to change the rules of the calculation.
• FILTER( ... ): The next argument is the filter - this is where we define the new rules.
• ALL('Date'): First, remove any and all filters from the 'Date' table. In a report visual, each row or data point has an inherent "filter context." For example, a row for January will only look at January data. ALL() wipes that slate clean so we can look at the entire date table.
• 'Date'[Date] <= MAX('Date'[Date]): This is the heart of the logic. It tells the FILTER function to create a new context that includes only the dates that are less than or equal to the "current" date in our visual. MAX('Date'[Date]) is clever - it effectively returns the latest date being considered at any given point in your table or chart.

So, when your chart is on the data point for March 15th, MAX('Date'[Date]) is March 15th. The formula then sums the [Total Sales] for all dates from the beginning of your 'Date' table up to March 15th.

Step 3: Add the Measure to a Visual

Now you can put your new measure to work! Create a table or a line chart visual:

• For the Axis (or Rows), use the 'Date'[Date] column from your date table.
• For the Values, add both your base measure [Total Sales] and your new [Running Total Sales] measure.

You’ll see the 'Total Sales' column showing sales for each individual period (day, month, etc.), while the 'Running Total Sales' column steadily climbs, accumulating the values as it goes.

A Simpler Approach: Using Time Intelligence Functions

While the FILTER(ALL(...)) pattern is a powerful and flexible technique, DAX also includes built-in time intelligence functions that can simplify certain types of running totals, like Year-to-Date (YTD).

If you just need a standard running total that resets at the end of each year, the TOTALYTD function is much cleaner.

How to Use TOTALYTD

Create another new measure with this simplified formula:

YTD Sales = TOTALYTD([Total Sales], 'Date'[Date])

That's it! This single line does the same work as our more complex formula but is specifically designed for Year-to-Date calculations. If you place [YTD Sales] in a visual along with date and total sales, you will see it accumulate from January onward and then reset back to zero on January 1st of the next year. DAX has similar functions for Quarter-to-Date (TOTALQTD) and Month-to-Date (TOTALMTD).

For a running total that does not reset annually, the FILTER(ALL(...)) method we explored first is the right approach.

Common Challenges and Troubleshooting

If your running total isn't working as expected, check these common issues:

1. The Total Is the Same on Every Row: This usually means the FILTER part of your calculation isn't working. Triple-check that your expression is 'Date'[Date] <= MAX('Date'[Date]). The MAX() part is critical for establishing the "current" date boundary.
2. Totals Don't Reset by Year (Or Another Category): The ALL('Date') function removes all filters from the entire date table. If you want the running total to respect the year (i.e., reset every new year), you need to keep that filter context. You can do this with ALLEXCEPT, for instance: ALLEXCEPT('Date', 'Date'[Year]) tells Power BI to remove all filters from the Date table except for the one on the 'Year' column.
3. Performance on Large Datasets: On massive datasets (millions of rows), the FILTER(ALL(...)) pattern can sometimes be slow. In these cases, optimizing your data model or using variables within your DAX measure can help speed things up. However, for most common use cases, its performance is perfectly fine.
4. Missing Date Table: Without a proper, continuous date table, your running totals might show gaps or behave unpredictably. This is the most common reason time intelligence calculations fail.

Final Thoughts

Understanding the DAX pattern for running totals is a massive step forward in your Power BI journey. By mastering the combination of CALCULATE, FILTER, and ALL, you unlock the ability to perform complex, context-based analysis that provides a much deeper narrative than looking at isolated data points.

Building these DAX measures and Power BI reports from scratch can be rewarding, but it often involves a lot of manual work and troubleshooting. At Graphed, we've designed an experience to skip the steep learning curve entirely. We allow you to connect your data sources like Google Analytics, Shopify, and your CRM, and then simply ask for the visual you need in plain English. For example, you can tell us to "create a line chart of cumulative Shopify revenue this year," and our AI instantly builds the interactive, real-time dashboard for you, no DAX required. We turned hours of formula-writing and report-building into a 30-second conversation, so you can focus on the insights and not the setup. You can try Graphed today and get straight to answering your most important business questions.