How to Create a Running Total in Power BI

Calculating a running total in Power BI is a fantastic way to see how a value accumulates over time, like tracking cumulative sales for the year or monitoring project budget spend. This article will walk you through several methods for creating running totals using DAX, from the simple Quick Measure for beginners to more flexible formulas for complex reports.

Why Running Totals are So Useful

A running total, also known as a cumulative sum, simply adds a value from the current period to the sum of all previous periods. Think of it as a snowball rolling downhill - it gets bigger as it goes. This calculation is a staple in business reporting for a few key reasons:

• Growth Tracking: See your year-to-date (YTD) or quarter-to-date (QTD) performance at a glance. Are you ahead of last year's pace? A running total makes this trend immediately obvious.
• Performance Monitoring: Easily track progress toward a specific goal. If your annual sales target is $1 million, a running total chart shows exactly how close you are each month.
• Contextualizing Data: A single month might look low, but a running total can show you're still on track overall. It smooths out the peaks and valleys, giving you a better sense of the big picture.

For example, if you see your daily website traffic, it might jump up and down. But a running total of traffic for the month will show a steady upward climb, which is much more useful for understanding your growth trajectory.

Before You Start: The Importance of a Date Table

Before you write a single line of DAX, the most crucial step is ensuring you have a proper Date table in your data model. While you might have dates in your sales or marketing data, using a dedicated calendar table is a Power BI best practice that makes all time-based calculations easier and more reliable.

Why is a Date Table non-negotiable?

Power BI's special “time intelligence” functions, which simplify calculations like year-over-year growth or moving averages, require a continuous set of dates to work correctly. A dedicated Date table guaranteed to have every single day of the year (with no gaps) satisfies this requirement.

Creating a Simple Date Table

If you don’t have one, creating one is simple. In Power BI, go to the Data view, select the Table tools tab, and click New table. You can use a simple DAX function like CALENDARAUTO() which automatically scans your entire model for the earliest and latest dates and builds a table to cover that range.

Date Table = CALENDARAUTO()

Once you've created this table:

1. Go to the Model view.
2. Drag the date column from your main data table (e.g., 'OrderDate' from your 'Sales' table) onto the 'Date' column in your new 'Date Table'. This creates the relationship.
3. Right-click on your new Date table and select Mark as date table. This tells Power BI to use it for all time-based logic.

With this foundation in place, you’re ready to build your running total calculations.

Method 1: The Quick and Easy Way with Quick Measures

If you're new to DAX, Power BI's Quick Measure feature is the perfect place to start. It provides a simple, menu-driven interface to create common calculations, and it will even write the DAX formula for you.

Here’s how to use it:

1. In the Report view, go to the Fields pane. Right-click on the table where your metric lives (e.g., your 'Sales' table) and select New quick measure.
2. In the window that appears, find the Calculation dropdown and scroll down to the Time intelligence section. Select Running total.
3. You'll see two main boxes to fill:
4. Click Add.

That's it! Power BI just created a new measure in your table. If you click on it, you'll see the DAX code it generated, which probably looks something like this:

Total Sales running total in Date = CALCULATE( SUM('Sales'[Total Sales]), FILTER( ALLSELECTED('Date Table'[Date]), ISONORAFTER('Date Table'[Date], MAX('Date Table'[Date]), DESC) ) )

This is a great way to learn what effective DAX looks like. You can see how it uses functions like CALCULATE, FILTER, and ALLSELECTED to get the job done. For many use cases, this is all you'll need.

Method 2: Manually Writing the DAX for More Control

While Quick Measures are fantastic, writing your own DAX gives you far more flexibility to customize the calculation. The most common and robust pattern for a running total involves the trinity of CALCULATE, SUM, and FILTER.

Right-click your table and select New measure, then enter the following formula:

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

This formula might look intimidating, but it’s quite logical once you break it down. Let's walk through what each part is doing:

• CALCULATE(...): This is arguably the most important function in DAX. It modifies the "filter context" of a calculation. In simple terms, it lets you change the rules of the calculation on the fly.
• SUM('Sales'[Sales Amount]): This is the easy part - the expression we want to evaluate. In this case, it’s just the sum of our sales.
• FILTER(...): This is the filter argument for CALCULATE. It tells CALCULATE what new set of rules to apply.
• ALL('Date Table'[Date]): Inside our filter, ALL() is mission-critical. It removes any existing filters from the 'Date' column. Why? Because if you have a table visual showing sales by month, each row is naturally filtered to only that month. ALL() wipes that slate clean, allowing our formula to "see" all the dates from the beginning of time up to the current point.
• `'Date Table'[Date] <= MAX('Date Table'[Date]):** This is the logic of the running total.

So, putting it all together, for each row in your visual, the formula says: "Calculate the total sales, but ignore the default date filter. Instead, sum up everything for all dates from the very beginning up to and including the last date for the current period."

Advanced Scenario: Running Total that Resets by Category

What if you need a running total that resets for different categories? For example, you want to see the cumulative sales for each product category over the year, but each category should start from zero.

This requires a small but important tweak to our DAX. We need to tell the ALL() function to remove the date filter but keep the category filter.

The perfect tool for this is the ALLEXCEPT() function.

Running Total by Category = CALCULATE( SUM('Sales'[Sales Amount]), FILTER( ALLEXCEPT('Sales', 'Sales'[Product Category]), 'Sales'[OrderDate] <= MAX('Sales'[OrderDate]) ) )

Here’s the difference: an ALLEXCEPT('Sales', 'Sales'[Product Category]) tells Power BI to "remove all filters from the 'Sales' table, except for the one applied to the 'Product Category' column." When you put this measure in a table or on a chart with the Product Category, the calculation for "Electronics" will only consider other "Electronics" sales, and a separate calculation will run for "Apparel." In your visual, you’ll see the line chart for the running total reset back to zero when a new category starts.

Visualizing Your Running Total Effectively

Once you've created your measure, you need to display it in a meaningful way. Here are two popular options:

1. The Combo Chart

The Line and Stacked Column chart is the classic way to visualize a running total.

• Put your date field (e.g., Month) on the Shared axis.
• Put your regular sales measure (e.g., Total Sales) on the Column values.
• Put your new running total measure on the Line values.

This gives you a beautiful chart showing the periodic performance (the bars) with an overlay of the cumulative performance (the line).

2. The Table or Matrix Visual

Don't underestimate a simple table for verifying your results. Create a table with three columns:

• Date
• Total Sales
• Running Total Sales

This allows you to quickly scan down the list and confirm that the numbers are accumulating correctly.

Final Thoughts

Mastering running totals in Power BI is a fundamental skill that unlocks a deeper understanding of your data trends. Whether you start with the simple Quick Measure or jump straight into writing custom DAX with a Date table, you now have the tools to track cumulative performance accurately and build more insightful reports for your team.

Writing and troubleshooting DAX is a powerful skill, but it often involves a lot of hunting for the right formula and debugging filter contexts. At Graphed, we created a tool to eliminate that friction completely. By connecting your data sources like Google Analytics, Shopify, or Salesforce, you can use plain English to ask for a report, like "Show me my running total of sales this year compared to last year as a line chart," and have it built for you in seconds - no DAX required.