How to Show Cumulative Totals in Power BI

Calculating a cumulative or running total is one of the most common and powerful ways to analyze performance over time. Instead of just looking at daily or monthly sales, a running total shows you how those figures are adding up, revealing the bigger picture and trends. This guide will walk you through exactly how to add cumulative totals to your reports in Power BI using a couple of different methods, from a quick-and-easy feature to custom DAX formulas.

What is a Cumulative Total, Anyway?

A cumulative total, also known as a running total, is the sum of a sequence of numbers that is updated each time a new number is added to the sequence. Think of it like a snowball rolling downhill - it gets bigger as it accumulates more snow.

In business analytics, this is incredibly useful for tracking progress toward a goal. Some common examples include:

• Year-to-Date (YTD) Sales: Tracking total sales from the beginning of the year up to the current date. This helps you compare current performance to the same point in previous years.
• Project Budget Tracking: Summing up all expenses to date to see how much of the budget has been spent and how much remains.
• Website Traffic Accumulation: Monitoring the total number of new users acquired since the start of a marketing campaign.

Showing this kind of metric in your dashboards gives stakeholders an immediate understanding of momentum and progress, which a simple daily or monthly view just can’t provide.

The Essential First Step: A Proper Date Table

Before you can perform any time-based calculations in Power BI, you need a solid foundation. That foundation is a dedicated Date Table. While Power BI can automatically create hidden date tables for you, relying on them can lead to problems down the line. Creating your own gives you full control and ensures your time intelligence functions work flawlessly.

A proper Date Table is simply a table that contains a continuous list of dates, with at least one column for the date itself. It should also have columns for Year, Quarter, Month Name, etc., to allow for easy filtering and grouping.

How to Create a Basic Date Table

The easiest way to create a Date Table is with a little bit of DAX (Data Analysis Expressions). Don't worry if you're new to it, this is a straightforward starting point.

1. In Power BI Desktop, navigate to the Model view on the left-hand side.
2. In the top Home ribbon, click on New Table.
3. A formula bar will appear. Enter the following DAX formula:

Date = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR ( [Date] ), "Month Number", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm" ), "Quarter", "Q" & FORMAT ( [Date], "q" ) )

This formula does two things:

• CALENDARAUTO() scans your entire model for the earliest and latest dates it can find and creates a row for every single day between them.
• ADDCOLUMNS() then adds extra, helpful columns like Year, Month Number, etc., to that table.

Mark it as a Date Table

Once your table is created, you need to tell Power BI that this is your official Date Table for calculations.

1. Select your new Date table in the Fields pane on the right.
2. Go to the Table tools tab at the top.
3. Click on Mark as date table, and in the dialog box, select the Date column.

Create the Relationship

The final step is to link your new Date Table to your data table (like a Sales, Orders, or Traffic table). In the Model view, find the date column in your main data table (e.g., OrderDate) and drag it onto the Date column in your new Date Table. This creates a one-to-many relationship, which is the key to making all our time-based measures work.

With that solid foundation in place, let's create our cumulative total.

Method 1: The Easy Way with a Quick Measure

Power BI's Quick Measure feature is a fantastic way to create common calculations without writing any DAX yourself. It guides you through the setup and writes the formula for you, which is also a great way to learn DAX.

Steps to Create a Running Total Quick Measure:

1. Go back to the Report view.
2. In the Fields pane, find the table containing the data you want to sum (e.g., your Sales table).
3. Right-click on the field you want a running total of (e.g., Revenue) and select New quick measure.
4. A dialog box will appear. Under the Calculation dropdown, scroll down to Time intelligence and select Running total.
5. Drag your main value field (e.g., Revenue) into the Base value box.
6. Drag the primary date column from your Date Table (e.g., 'Date'[Date]) into the Date box.
7. Click Add to create the measure.

You'll now see a new measure in your Fields pane, likely named something like "Revenue running total in Date". You can use this new measure in any visual. When Power BI builds this for you, it actually writes the DAX code in the background. You can click on the measure to see the formula it generated, which is a great way to start learning what DAX looks like.

Method 2: Full Control with Your Own DAX Measure

Quick Measures are great, but for more flexibility and a deeper understanding, writing your own DAX is the way to go. It gives you full control over how the calculation works. We’ll cover two powerful ways to do this.

Running Total with CALCULATE and FILTER

This is the classic, manual DAX pattern for a running total. It looks a little intimidating at first, but it’s quite logical once you break it down.

1. Right-click on your data table (e.g., Sales) and select New Measure.
2. In the formula bar, enter this DAX formula:

Running Total Revenue = CALCULATE( SUM('Sales'[Revenue]), FILTER( ALLSELECTED('Date'), 'Date'[Date] <= MAX('Date'[Date]) ) )

Let’s break this down piece by piece:

• SUM('Sales'[Revenue]): This is the simple expression we want to calculate. On its own, it would just give us the total revenue for the current context (like a single day or month in a chart).
• CALCULATE(...): This is the most important function in DAX. It modifies the context in which your formula is calculated. Here, it’s going to take our standard sum and apply a new, custom filter to it.
• FILTER(...): This function defines our new filter context.

Year-to-Date (YTD) Total with Time Intelligence Functions

If your specific goal is to calculate a year-to-date total that resets every year, DAX has built-in "Time Intelligence" functions that make this incredibly simple. There's no need for the complex FILTER or ALLSELECTED logic.

1. Right-click your table and select New Measure.
2. Enter this beautifully simple formula:

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

That’s it! The TOTALYTD function handles everything for you. It takes two arguments:

• The expression to calculate (SUM('Sales'[Revenue])).
• The date column from your dedicated Date Table ('Date'[Date]).

This measure will automatically sum the revenue from the beginning of the year up to the current date and will reset to zero on January 1st of each subsequent year. Power BI also has similar functions like TOTALQTD() for Quarter-to-Date and TOTALMTD() for Month-to-Date.

Visualizing Your Cumulative Total

Once you've created your measure (either with Quick Measures or your own DAX), using it is easy. Just drag it from the Fields pane onto a visual.

The best way to show a running total is usually with a Line and clustered column chart.

• Use your date field (e.g., 'Date'[Month Name]) on the X-axis.
• Put your regular sum measure (e.g., Total Revenue) in the Column y-axis field.
• Put your new running total measure (e.g., Running Total Revenue) in the Line y-axis field.

This creates a powerful chart that shows your monthly performance as bars, with a line soaring over them showing the cumulative growth throughout the period.

You can also use the measure in a Table or Matrix visual to see the exact numbers side-by-side, which is great for detailed reports.

Final Thoughts

We've looked at how to establish a proper Date Table and use both Quick Measures and custom DAX to calculate running totals in Power BI. Understanding these patterns, especially functions like CALCULATE and TOTALYTD, unlocks a massive amount of analytical power for tracking performance and momentum over time.

Once you get comfortable with creating custom measures, Power BI becomes an even more powerful tool for data analysts. By investing time in understanding DAX, you can tailor your dashboards to provide insights that guide strategic business decisions effectively.