DAX

Dax Function: RUNNINGSUM

Q: Does Power BI have a built-in RUNNINGSUM function?

No, but it can be implemented using DAX expressions like CALCULATE and FILTER.

Category: Filter Functions

The RUNNINGSUM function in Power BI, typically implemented using DAX expressions, calculates a cumulative total over a defined set of rows. It enables tracking of running totals across a dataset, often in time-series or sequential data.

Purpose

Cumulative Aggregation: Accumulates values row by row based on a specific order.
Trend Analysis: Tracks progression over time or other dimensions.
Dynamic Visualization: Supports rolling totals in dashboards and reports.

Type of Calculations

Additive Progression: Sum of all preceding values, including the current one.
Context-Sensitive Computation: Based on filters, sort orders, or partitions.

Practical Use Cases

Revenue Tracking: Display cumulative sales over months.
Inventory Management: Calculate stock levels over time.
Financial Reporting: Represent year-to-date (YTD) expenses or incomes.


RUNNINGSUM = CALCULATE(SUM(Table[Column]), FILTER(ALL(Table[Column]), Table[Column] &lt;= EARLIER(Table[Column])))

Parameter	Type	Description
`Table[Column]`	Column	The column containing values to sum cumulatively.
`ALL(Table[Column])`	Function	Removes filters to consider all rows in the table or within the defined scope.
`EARLIER(Table[Column])`	Function	Refers to the current row value in the iteration context.

How Does RUNNINGSUM Dax Works

The RUNNINGSUM function iterates through rows in a table, summing all preceding values, including the current row’s value. This is typically achieved using a combination of CALCULATE, FILTER, and EARLIER in DAX.

What Does It Return?

Numeric Value: Represents the cumulative total for each row in the dataset.

When Should We Use It?

Time-Series Analysis: Analyze cumulative metrics over days, weeks, months, or years.
Rolling Totals: Calculate dynamic running totals across filtered datasets.
Visual Representation: Use in line charts or tables to depict trends.

Examples

Basic Usage :

Calculate running total for a Sales column:


RUNNINGSUM =
CALCULATE(
SUM(Sales[Amount]),
FILTER(ALL(Sales), Sales[Date] &lt;= EARLIER(Sales[Date]))
)

Column Usage

Create a running total within specific categories:


RUNNINGSUM =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales),
Sales[Category] = EARLIER(Sales[Category]) &&
Sales[Date] &lt;= EARLIER(Sales[Date])
)
)

Advanced Usage

Combine with DATEADD for rolling cumulative totals:


RUNNINGSUM =
CALCULATE(
SUM(Sales[Amount]),
DATESYTD(Sales[Date])
)

Tips and Tricks

Order Matters: Ensure proper sorting of rows for accurate calculations.
Filter Scope: Use ALL and EARLIER effectively to manage context.
Visualization Support: Running totals are highly effective in line or bar charts for time-series data.

Performance Impact of RUNNINGSUM DAX Function:

Dataset Size: Running totals over large datasets can be computationally expensive.
Sorting: Ensure rows are sorted correctly for consistent results.
Optimization: Minimize the use of EARLIER in complex datasets; consider measures instead.

Related Functions You Might Need

SUMX: Performs row-by-row aggregations.
DATESYTD: Calculates cumulative totals for year-to-date.
CALCULATE: Alters context for aggregation.
FILTER: Applies row-level filtering for calculations.

Want to Learn More?
For more information, check out the official Microsoft documentation for RUNNINGSUM You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. Does Power BI have a built-in RUNNINGSUM function?

No, but it can be implemented using DAX expressions like CALCULATE and FILTER.

2. How does RUNNINGSUM handle filters?

It typically resets or adapts based on the filter context unless overridden by ALL.

3. Can I calculate running totals within partitions?

Yes, use additional filters to define partitions, such as categories or regions.

4. Is RUNNINGSUM resource-intensive?

Yes, especially on large datasets. Optimization techniques are essential.

5. What visualizations work best with RUNNINGSUM?

Line charts, area charts, and cumulative tables are ideal.

Dax Function: ROWNUMBER

Dax Function: SELECTEDVALUE