Others

How to Create a Conditional Running Total in Power BI

Ayan Masood Sep 27, 2026 869 views

Question details

The user needs to calculate a conditional running total in Power BI that is grouped separately for each order value and sorted by calendar date, while keeping rows with identical dates separate.

Product: Power BI
Device & OS: not provided
Scenario: Performing advanced data modeling where a cumulative sum must be calculated conditionally based on specific categories and chronological order.
Observed behavior: The user seeks a specific DAX formula to accurately calculate the running sum without aggregating rows that share the same date into a single line.

Before you start

Ensure your dataset is loaded into Power BI Desktop and that your 'Order', 'Calendar Date', and 'Actual' value columns are properly formatted with the correct data types.

Solution 1Recommended

Use a DAX Calculated Column for the Running Total

Implement a combination of SUMX and FILTER functions in DAX to dynamically calculate the running total based on your specific conditions.

This DAX formula utilizes the EARLIER function to compare the current row's order and date values against the rest of the table, ensuring the cumulative sum is partitioned correctly by order and sorted by date.

Open Data View

Launch your Power BI report and navigate to the 'Data' view using the left-hand sidebar to see your table.

Create a New Column

Select your table, go to the 'Table tools' or 'Modeling' tab on the ribbon, and click on 'New column'.

Enter the DAX Formula

In the formula bar, paste the following DAX code: Running Total = SUMX(FILTER('TableName', EARLIER('TableName'[Order]) = 'TableName'[Order] && EARLIER('TableName'[Calendar Date]) >= 'TableName'[Calendar Date]), 'TableName'[Actual]). Replace 'TableName' with your actual table's name.

Apply the Formula

Press Enter to execute the formula. A new column will appear showing the conditional running total for each order.

Use a DAX Calculated Column for the Running Total

Formula Customization: If you have duplicate dates within the same order and need a strict row-by-row running total, consider adding an Index column and appending an Index condition to the FILTER function.

Solution 2

Consult the Power BI Community for Complex Models

If your data model contains complex relationships or millions of rows causing performance issues, seek specialized DAX optimizations.

WPS Spreadsheet Data Analysis

Calculate Conditional Running Totals Easily in WPS Spreadsheet

If you do not require the complex environment of Power BI, you can quickly calculate conditional running totals using the SUMIFS function in WPS Spreadsheet. It is an excellent tool for efficient daily data analysis.

1. Open Your Data: Launch WPS Spreadsheet and open your dataset containing the Order, Date, and Value columns.
2. Sort the Dataset: Highlight your data, go to the 'Data' tab, and sort primarily by 'Order' and secondarily by 'Date' in ascending order.
3. Input the SUMIFS Formula: In a blank column next to your first data row (e.g., cell D2), enter the formula: =SUMIFS($C$2:C2, $A$2:A2, A2) assuming Column C contains the values and Column A contains the Orders.
4. Apply to the Entire Column: Press Enter to calculate the first value, then double-click the small green square at the bottom-right of the cell to drag the fill handle down to the bottom of your dataset.

Lightweight and free data analysis toolFully compatible with Microsoft Excel (.xlsx) formatsSupports advanced statistical functions like SUMIFSFamiliar interface for quick and seamless calculation

microsoft office alternative - wps office

Frequently Asked Questions

What does the EARLIER function do in my Power BI running total formula?

The EARLIER function retrieves the value of a column in the current row context before the FILTER function evaluates the entire table. This allows Power BI to compare the current row's 'Order' and 'Date' against all other rows to calculate a cumulative sum.

Why is my DAX running total calculating for the whole table instead of by order?

This happens if the partitioning condition is missing or incorrect. Make sure your FILTER function includes the condition EARLIER('TableName'[Order]) = 'TableName'[Order], which explicitly tells Power BI to reset or group the running total for each specific order.

How can I handle duplicate dates in a Power BI running total?

If multiple rows share the exact same order and date, the standard formula will assign the same total to those rows. To keep them separate, create an Index column in Power Query, then add an additional condition to your DAX formula like: && EARLIER('TableName'[Index]) >= 'TableName'[Index] to break the tie.

Can I use variables (VAR) instead of the EARLIER function in DAX?

Yes. Modern DAX best practices recommend using variables. You can define VAR CurrentOrder = 'TableName'[Order] and VAR CurrentDate = 'TableName'[Calendar Date], and then use a CALCULATE(SUM('TableName'[Actual]), FILTER(ALL('TableName'), 'TableName'[Order] = CurrentOrder && 'TableName'[Calendar Date] <= CurrentDate)) structure, which is generally easier to read and debug.

Office Quick Solutions