Cumulative Total

ElliotP

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] ) )

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.

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:

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

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

Thanks,

Ryan Durkin

Did you read this article? It really explains everything you need to know about cumulative total

As I try to learn Power BI,

I have face the same question also but the data i have may have slightly different than the above situation.

I have a sample data which are the sales data of some countries.

I want to find the cumulative total sales but I have sales which happen more than once in the same day.

I have try to apply the above formula but its only apply on 'year' level but still the value are incorrect also.

Here is what I intend to get: Chart I want to have

Here are the sample data: Sample data picture

Here's the formula I try to apply: Formula of cumulative total I tried to apply

(where the date here are the 'Order Date' and with the value which is 'Sales')

Really appreciate if any one can have reply, if there is anything you want, I will provide.

Thank alot!

Hi,

I got this expression working in a bar chart so that it shows running total correctly when [Date] field is selected as x-axis. However, if I use date hierarchy as x-axis, it won't work anymore. Instead of running total, it gives period's total. E.g. if Total Sales in January is 100 and 200 in February, it displays 100 for January and 200 for February, instead of 100 for Jan and 300 for Feb as it should. Does anyone know what might be the issue?

CALCULATE (
    SUM ( [TotalSales] );
    FILTER(
	ALL ( [Sales] );
        Sales[Date] <= MAX( Sales[Date] )
    )
)

Hi,

I am facing problems with getting the right DAX expressions.

The Running Total column is calculated manually... this is the result that I want to achieve with the Running Total Measure. The Running Total sums up for each Team and across the dates.

I'm using the current DAX expression, but it is running total regardless of the Team.

Running Total Measure =
    CALCULATE(
        SUM(Sheet1[Qty]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])))

Can someone help? Thanks.

Hi,

I have been trying to cumulative sum for a long time, but I couldnt it. I give the example below, İf you can help me I realy will be grateful. Thank you for your considiration. God bless you.

I try to 2017-01 + 2017-02 and try to write new sheet . I give you excel example. 

Why in measure we use MAX () but use Earlier () in the calculated column?  Are they exchangable ?

Anyone know how to get cumulative total by site?

I used the solutions provided although I needed to reset the sum every week, this worked well for me. Added a variable for the week number to compare against. Adds each day as it goes until it reaches a new week, starts again.

Running SOP = 
var x = SOP[Week Number]

return
CALCULATE (
    SUM ( 'SOP'[Production Tonnes])
    , ALL (SOP),
    ('SOP'[Date] <= EARLIER ( 'SOP'[Date]) && weeknum(SOP[Date],2) == x))

 

 

Hi There,

I tried as suggested but only get it working almost.

Because there are no actuals from 01/01/2021 onwards the last value will be shown. How can I show the cumulative only if there is an actual value?

Thanks in advance!

Cheers,