Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Cumulative (Running) Totals in DAX

A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals.  For example, you may want to see total sales of a product as it accumulates over time, or for inventory models the total on hand at a given time.  You can find more tips and tricks at my blog, www.bipatterns.com.

Let’s start with a base measure in a very simple pivot table.
Total Sales :=
CALCULATE ( SUM ( FactSales[SalesAmount] ) )

Total Sales

Now lets take our first attempt at computing a running total.  This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away.
Cumulative Total Sales :=
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( DimDate[Datekey] ),
        DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
    )
)
Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed.  The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ).  This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.

If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.

Cumalative Total Sales

The formula returns a number for dates that have no sales.  We need to add some error handling, which is outlined below.
Cumulative Sales (Correct) :=
IF (
    COUNTROWS ( FactSales ) > 0,
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( DimDate[Datekey] ),
            DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
        )
    ),
    BLANK ()
)

The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank.  You can see the difference between the two measures below:

Cumalative Total Sales (Correct)

If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

1 REPLY 1
technolog
Super User
Super User

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

 

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.