How to Calculate Rolling Sum in Power BI

Calculating a rolling sum, or a cumulative total, turns a standard report into a powerful trend analysis tool. Instead of just seeing daily sales, you can track performance over time, identify momentum, and spot patterns you might otherwise miss. This article will show you exactly how to create a rolling sum in Power BI using DAX (Data Analysis Expressions).

What Exactly is a Rolling Sum (or Cumulative Total)?

A rolling sum calculates the total of a metric up to a certain point in time. It adds the value from the current period (like a day or month) to the total of all previous periods. For example, if your sales were $100 on Monday, $150 on Tuesday, and $50 on Wednesday, the rolling sum would look like this:

• Monday: $100
• Tuesday: $250 ($100 from Mon + $150 from Tue)
• Wednesday: $300 ($250 from Tue + $50 from Wed)

This is incredibly useful for visualizing growth and understanding how performance accumulates. You can easily answer questions like, "Are we on track to hit our quarterly sales goal?" or "How does our website traffic this year compare to the same point last year?"

Before You Start: The Importance of a Date Table

Before writing a single line of DAX, the most critical step is ensuring you have a proper Date table in your data model. A Date table is a separate table that contains a continuous list of dates, along with useful attributes like year, quarter, month, and day of the week.

Why is this so important?

• Time Intelligence Functions: Most of Power BI’s powerful time-based DAX functions require a complete, unbroken list of dates to work correctly. Your sales data might have gaps (e.g., no sales on a Sunday), but your Date table won't.
• Consistency: It provides a single source of truth for all time-related analysis, making your formulas more reliable and easier to manage across your entire report.

If you don’t have one, you can easily create one using Power Query or with DAX using the CALENDAR() or CALENDARAUTO() functions. Once created, make sure to mark it as a date table in Power BI by selecting the table, going to the "Table tools" tab, and clicking "Mark as date table". Then, create a relationship between your Date table's date column and the date column in your main data table (e.g., 'OrderDate').

Calculating a Rolling Sum with DAX: The Step-by-Step Method

We'll walk through creating a rolling sum measure using DAX. For this example, let's assume you have a 'Sales' table with a [SalesAmount] column and a 'Date' table with a [Date] column.

Step 1: Create a Base Measure

First, it's always best practice to create a base measure for the value you want to sum up. This makes your DAX formulas cleaner and easier to debug later. If you don't have one already, create a simple measure for your total sales.

Navigate to the Modeling tab, click "New Measure," and enter the following formula:

Total Sales = SUM('Sales'[SalesAmount])

Step 2: Write the Rolling Sum DAX Formula

Now it's time to build the measure that calculates the cumulative total. There are a few ways to do this, but we'll focus on the most flexible and common approach. This pattern works for almost any scenario.

Create another new measure and use this DAX formula:

Running Total Sales = CALCULATE ( [Total Sales], FILTER ( ALLSELECTED ( 'Date' ), ISONORAFTER ( 'Date'[Date], MAX ( 'Date'[Date] ), DESC ) ) )

Let's take a moment to understand what this formula is doing because it's pretty powerful. Power BI's DAX formulas work within a concept called "filter context," which means that any calculation is filtered by the data in its current view (like the day, month, or product category in a chart).

• CALCULATE([Total Sales], ... ): This is the workhorse of DAX. It tells Power BI to calculate the [Total Sales] measure, but with modified filters. The parts inside CALCULATE are the new rules it will follow.
• FILTER(...): This function tells Power BI to look at a specific table and keep only the rows that meet a certain condition.
• ALLSELECTED('Date'): This is a key part. It tells the formula to temporarily remove any existing filters on the 'Date' table that come from the visual itself (like the month on the axis of a chart), but to respect any filters coming from slicers or other charts on the page. This is important so our calculation can see all the dates it needs to sum up, but still be responsive to a Year slicer, for example.
• ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC): This is the logic where the magic happens. It's a newer, more efficient way to express 'Date'[Date] <= MAX('Date'[Date]). For each row in your visual (let's say it's for January 15th), MAX('Date'[Date]) gets that "current" date (Jan 15th). The formula then checks the entire 'Date' table (thanks to ALLSELECTED) and keeps every single date that is on or before January 15th. CALCULATE then sums the [Total Sales] for all of those dates.

Step 3: Add the Measure to a Visualization

Now that your measure is ready, you can see it in action.

1. Create a table or a matrix visual.
2. Add your date field (e.g., 'Date'[Date]) to the Rows.
3. Add your base measure (Total Sales).
4. Add your new measure (Running Total Sales).

You will see that 'Total Sales' shows the value for each specific day, while 'Running Total Sales' shows a value that grows with each passing day. For even better visualization, add the 'Running Total Sales' measure to a line chart with your 'Date'[Date] column on the axis to see a clear upward trend line.

Alternative Method: Rolling Sum for a Fixed Period (e.g., Last 30 Days)

Sometimes you don't need a running total for all time, you might just want a rolling sum for a specific period, like the last 30 days or 12 months. This is great for tracking recent momentum.

DAX time intelligence functions are perfect for this. Let's create a 30-day rolling sales measure.

Create a new measure with this formula:

30-Day Rolling Sales = CALCULATE( [Total Sales], DATESINPERIOD( 'Date'[Date], LASTDATE('Date'[Date]), -30, DAY ) )

Here's the breakdown:

• DATESINPERIOD('Date'[Date], start_date, number_of_intervals, interval): This function returns a table of dates.
• LASTDATE('Date'[Date]): This acts as our start_date. In the context of a chart, it grabs the most recent date for a given data point.
• -30, DAY: This tells the function to go back 30 days from the start_date.

Dropped into a chart, this measure will show, for any given day, the sum of total sales for that day and the 29 days prior. This is a powerful way to smooth out daily fluctuations and see the underlying trend.

Common Challenges and Pitfalls

When creating rolling sums, you might run into a few common issues. Here’s what to look out for.

• Missing a Date Table: As mentioned, this is the #1 cause of headaches. The logic breaks down if there are gaps in your dates. Always build and use a dedicated Date table.
• Incorrect Relationships: Ensure your Date table is properly linked to your data/fact table on the date column. A single, one-to-many relationship (from the Date table to the data table) is what you want.
• Using the Wrong Filter Remover: There's a subtle but important difference between ALL() and ALLSELECTED(). A good rule of thumb is to start with ALLSELECTED(), as it makes your visuals play nicely with page-level slicers. Use ALL() only when you need to completely ignore all filters on a table, which is less common for rolling sums.

Final Thoughts

By learning just a few core DAX functions like CALCULATE and FILTER, you can build powerful rolling sum calculations that provide deep insight into your business trends. The key is to start with a solid data model featuring a proper date table and then layer in your DAX logic piece by piece, understanding how each part modifies the filter context to get you the right result.

Of course, becoming proficient in DAX takes time, and the process often involves a lot of trial and error. If you'd rather get straight to the insights without wrangling formulas, we designed Graphed to automate that process for you. You can connect your data sources like Google Analytics, Shopify, or Salesforce, and then simply ask for the report you want in plain English, like "Show me my running total sales this year as a line chart." We handle the complex calculations in the background, delivering a live, interactive dashboard in seconds, so you can spend less time writing code and more time making great decisions.