How to Calculate a Cumulative Sum in Power BI: A Step-by-Step Guide

The crucial formula uses the CALCULATE function to aggregate the data while imposing specific temporal constraints:

Cumulative Sum = 
CALCULATE (
    SUM ( 'my_data'[Sales] ),
    ALL ( 'my_data' ),
    'my_data'[Date] <= EARLIER ( 'my_data'[Date] )
)

This particular example creates a new column named Cumulative Sum that contains the cumulative sum of the values in the Sales column of the table named my_data. This method is highly effective for building running totals directly into your data model.

Breaking Down the DAX Formula Components

To fully appreciate why this pattern works, we must analyze the role of each function within the context of the running total calculation:

1. CALCULATE: This is the engine of the operation. CALCULATE is designed to evaluate an expression (in this case, SUM('my_data'[Sales])) within a modified filter context. It takes the filters passed to it and applies them on top of or in place of the existing filters in the evaluation environment.
2. SUM('my_data'[Sales]): This is the aggregation expression. It tells DAX what value to total up. In our scenario, we are summing the Sales column.
3. ALL ('my_data'): This is a powerful filter modifier. When the formula is being evaluated row by row, the current row context automatically filters the table to just that one row. ALL('my_data') is necessary to clear all existing filters on the entire my_data table. This effectively makes every row in the table visible again before the next filter condition is applied, ensuring we don’t accidentally restrict the sum to just the current row.
4. 'my_data'[Date] <= EARLIER ('my_data'[Date]): This is the temporal constraint that defines the cumulative nature of the sum. The EARLIER function is critical in a row context: it captures the value of the Date column from the outer row context (i.e., the date of the row currently being calculated). The resulting filter condition then says, “Only include rows where the Date is less than or equal to the date of the current row.” This is what creates the running total effect, ensuring only historical data is included in the sum.

Understanding the interplay between CALCULATE, which modifies context, and ALL and EARLIER, which define the scope of the modification, is essential for mastering running totals in Power BI.

Practical Example Setup: Preparing the Data

To illustrate the implementation, let us consider a typical sales tracking scenario. Suppose we have the following table loaded into our Power BI model, named my_data, containing sales figures recorded on various dates. It is crucial that the table includes a proper date column to correctly sequence the running total calculation.

The following example shows how to use this syntax in practice.

Example: How to Calculate a Cumulative Sum in Power BI

Suppose we have the following table in Power BI named my_data that contains information about sales made on various dates by some company. Notice the structure, with distinct dates and corresponding sales figures, ready for chronological aggregation:

Our objective is to incorporate a new column that meticulously tracks the cumulative sum of the values in the Sales column, allowing us to visualize how total sales grow throughout the dates provided in the dataset. This column will be a Calculated Column, meaning it will be computed and stored within the table structure itself.

Step-by-Step Implementation of the Cumulative Sum

The implementation requires accessing the data modeling tools within Power BI Desktop and introducing the DAX formula we discussed previously.

Step 1: Create a New Calculated Column

To begin, navigate to the Data View or Model View within Power BI Desktop. Click the Table tools tab along the top ribbon, then click the New column icon. This action opens the formula bar, ready for the DAX expression input:

Step 2: Input the DAX Formula

Now, meticulously type or paste the comprehensive running total formula into the formula bar. Ensure that table and column names (e.g., 'my_data' and [Sales]) exactly match the names within your data model. The formula is:

Cumulative Sum = 
CALCULATE (
    SUM ( 'my_data'[Sales] ),
    ALL ( 'my_data' ),
    'my_data'[Date] <= EARLIER ( 'my_data'[Date] )
)

After pressing Enter, Power BI will process this formula for every single row in the my_data table, performing the sophisticated context modification required to generate the running total based on the date sequence.

Analyzing the Results and Context Transition

Upon successful execution of the DAX formula, a new column named Cumulative Sum will be appended to the my_data table. This column displays the precise running total of sales up to and including the date in that row. The resulting table confirms that the context modification was successful:

We can validate the results by manually tracing the calculation through the first few rows. The context transition facilitated by CALCULATE and the filtering applied by EARLIER ensures that each step correctly builds upon the preceding sums:

• Cumulative sum of sales for first row (Date 1/1/2024): The filter includes only that row. Result: 6
• Cumulative sum of sales for second row (Date 1/2/2024): The filter includes sales up to and including this date. Calculation: 6 + 9 = 15
• Cumulative sum of sales for third row (Date 1/3/2024): The filter includes sales up to and including this date. Calculation: 6 + 9 + 13 = 28
• Cumulative sum of sales for fourth row (Date 1/4/2024): The filter includes sales up to and including this date. Calculation: 6 + 9 + 13 + 12 = 40

The running total increases monotonically, reflecting the precise accumulation of sales over time. This resulting column is now ready to be used in visualizations, such as line charts, to easily track growth trends.

Advanced Considerations and Best Practices

While the Calculated Column approach demonstrated here is ideal for static running totals that do not need to respond to external filters (like slicers on a report page), for dynamic analysis, users should consider creating a Measure instead. A measure-based cumulative sum typically uses the FILTER function combined with ALL or ALLSELECTED over the date column, allowing the total to dynamically recalculate when visual filters are applied.

Regardless of whether you choose a column or a measure, understanding the concepts of row context, filter context, and how CALCULATE transitions between them is the key to generating accurate and efficient running totals in Power BI.

The following tutorials explain how to perform other common tasks in Power BI: