How to Add Cumulative Sum in Power BI

Calculating a running total is one of the most common and useful tasks in data analysis. Whether you’re tracking year-to-date sales, campaign sign-ups, or website sessions, a cumulative sum helps you see performance trends over time, not just in isolated moments. This tutorial will walk you through exactly how to create a cumulative sum in Power BI using DAX, from the basic formula to more advanced applications.

What is a Cumulative Sum?

A cumulative sum, also known as a running total, is the accumulation of a sequence of numbers. In a business context, it answers questions like:

• What are our total sales so far this year (Year-to-Date)?
• How has our user base grown day-by-day since our product launch?
• What is the total amount spent on our ad campaigns up to this point in the quarter?

Instead of showing you a standalone value for 'sales on January 5th,' a cumulative sum shows you the total of all sales from January 1st through January 5th. This gives you a clear view of your growth trajectory.

The Essential DAX Functions You'll Need

Before we build the formula, it’s helpful to understand the key DAX (Data Analysis Expressions) functions that make cumulative sums possible. Don't worry if you're not an expert - we'll break down exactly what each one does.

• CALCULATE(): This is the most powerful function in DAX. Its job is to modify the "context" in which a calculation is performed. In simple terms, it lets us say, "Calculate this sum, but ignore the normal date filters and use these new rules instead."
• SUM(): A straightforward function that adds up all the numbers in a column. This will be our base calculation.
• FILTER(): This function iterates over a table and returns a table that only includes rows meeting specific criteria. We'll use it to define our "new rules" for the CALCULATE function.
• ALL(): A crucial function that removes filters from a table or column. We need this to tell Power BI to temporarily ignore the specific date of a row so we can look at all dates up to that point.
• MAX(): When used within a visual, MAX() returns the 'current' highest value in the given context. For a table with dates, MAX([Date]) tells you the specific date of the current row you're calculating for.

Step-by-Step Guide: Creating a Cumulative Sum by Date

Let's build a cumulative sales measure from scratch. For this example, we'll assume you have a Sales table with a [Revenue] column and a [OrderDate] column.

Prerequisite: Create a Dedicated Date Table

The most important best practice in Power BI for any time-based analysis is to have a dedicated Date table. Using the date column directly from your fact table (like your Sales table) can cause problems if there are days with no sales, as these dates will be missing.

A Date table ensures you have a continuous, unbroken sequence of dates.

You can create a basic one easily:

1. In Power BI Desktop, go to the Modeling tab and click New Table.
2. Enter the following DAX formula and hit Enter: DateTable = CALENDARAUTO()
3. Power BI will automatically scan your model for the earliest and latest dates and create a table with a single [Date] column covering that entire range.
4. Next, go to the Model view and create a relationship by dragging the [Date] column from your new DateTable to the [OrderDate] column on your Sales table.

Step 1: Create a Base Measure for Your Sum

Before you calculate a running total, you need a basic measure for the value you want to accumulate. This isolates the logic and makes your DAX easier to read and troubleshoot.

On the Home tab, click New Measure and enter: Total Revenue = SUM(Sales[Revenue])

Step 2: Write the Cumulative Sum DAX Formula

Now for the main event. Click New Measure again and enter the following core formula for a cumulative sum:

Cumulative Revenue = CALCULATE( [Total Revenue], FILTER( ALL('DateTable'[Date]), 'DateTable'[Date] <= MAX('DateTable'[Date]) ) )

Step 3: Understanding the Formula Piece-by-Piece

This formula might look complex at first, but it follows a logical pattern that tells Power BI exactly what to do.

• CALCULATE([Total Revenue], ...): This outer shell tells Power BI, "we want to compute the [Total Revenue] measure, but we're going to apply some special filter conditions first."
• FILTER(...): This is the argument where we define those special conditions. It’s creating a temporary, virtual table for CALCULATE to use.
• ALL('DateTable'[Date]): This is the most critical part. Let's say your report is a table showing one row for each day. The ALL function says, "For the row dated January 5th, I want you to temporarily ignore that filter and give me access to every single date in the DateTable." This allows the calculation to "see" January 1st, 2nd, 3rd, and 4th as well.
• 'DateTable'[Date] <= MAX('DateTable'[Date]): After removing the filter with ALL, we add a new one. This part does the actual "cumulative" work. MAX('DateTable'[Date]) gets the date of the current row being calculated (e.g., January 5th). So the complete phrase tells the formula: "Out of all the dates you now see, only keep the ones that are on or before the current date in our visual."

The result? For the row of January 5th, the formula sums the [Total Revenue] for all dates from the beginning up until January 5th. For the January 6th row, it does the same, summing all revenue up until January 6th, and so on.

Step 4: Add the Measure to a Visual

Now you can put your new measure to work! Create a Table or Line Chart visual.

• Add the Date column from your DateTable to the visual (use it for the Axis on a line chart or as a column in a table).
• Add your base measure, [Total Revenue].
• Add your new measure, [Cumulative Revenue].

You should now see your daily revenue figures alongside a clean running total that grows over time.

Advanced Scenarios and Variations

Once you've mastered the basic pattern, you can adapt it for different business needs.

Cumulative Sum That Resets by Year (Year-to-Date)

What if you want your running total to start over on January 1st of each year? We just need to add one more condition to our FILTER argument to ensure the year of the dates we're summing matches the year of the current row.

You can use Power BI's built-in time intelligence functions for this, which is often simpler: YTD Revenue = TOTALYTD([Total Revenue], 'DateTable'[Date])

However, understanding how to build it manually gives you more flexibility and deepens your DAX knowledge. Here's the same logic written out: YTD Revenue (Manual) = CALCULATE( [Total Revenue], FILTER( ALL('DateTable'), 'DateTable'[Year] = MAX('DateTable'[Year]) && 'DateTable'[Date] <= MAX('DateTable'[Date]) ) )

Note: a 'Year' column would need to be added to your DateTable, which you can do with a calculated column: Year = YEAR([Date]).

Cumulative Totals Over Non-Date Categories

Sometimes you need a running total over a category, like products sorted by sales amount. This is a bit trickier because categories don't have a natural sequence like dates. The key is to first establish an order (e.g., rank your products) and then modify the DAX pattern.

Let's say you have a table of Products and you want a cumulative sum of revenue, starting with the highest-selling product.

Step 1: Create a Rank Measure for Products.

Product Rank = RANKX(ALL('Products'[Product Name]), [Total Revenue], , DESC)

Step 2: Create the Cumulative Measure.

Cumulative Revenue by Product Rank = VAR currentRank = [Product Rank] RETURN CALCULATE( [Total Revenue], FILTER( ALL('Products'[Product Name]), RANKX(ALL('Products'[Product Name]), [Total Revenue], , DESC) <= currentRank ) )

In this formula, we first store the rank of the current product in the visual. Then, we ask CALCULATE to sum the revenue for all products whose rank is less than or equal to that current product's rank. This creates a running total down your list of products.

Common Pitfalls and How to Fix Them

• Running total is the same as the daily total: This almost always means the core filtering logic is wrong. Double-check that you are using ALL('DateTable'[Date]) inside your FILTER function. Without it, the calculation can only 'see' one date at a time.
• Running total is the grand total on every row: This means you've successfully removed filters with ALL() but haven't correctly applied the second part of your FILTER logic ('DateTable'[Date] <= MAX('DateTable'[Date])).
• Blank or incorrect values: Make sure your Date table is marked as a date table in Power BI (go to table view, select the table, and set it under Table tools). Also, confirm that the relationship between your Date table and your fact table is active and correctly configured on the date columns.

Final Thoughts

Creating cumulative sums with DAX opens up powerful new ways to analyze your data in Power BI. Understanding the CALCULATE, FILTER, and ALL pattern is fundamental, and once you grasp it, you can apply the same logic to many different kinds of calculations beyond a simple running total.

At Graphed, we’ve focused on removing this kind of technical complexity from data analysis entirely. Our tool is designed to turn hours of data wrangling and DAX formula-writing into simple, seconds-long conversations. Instead of building measures and setting up visual contexts, you can just ask in plain English, "Show me a line chart of our cumulative revenue this year compared to last year," and our AI data analyst builds a live, interactive dashboard for you instantly.