cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElliotP
Post Prodigy
Post Prodigy

Cumulative Total

Hi,

 

I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, trying it as both a measure and a calc'd column; It always seems to refer me to the same number. So for example; March will be 10, April 12, but instead of showing me 10 for March and 22 for April, it shows me 10 for March and 12 for april.

 

For eg: https://gyazo.com/41bd333cedac290e6980772906ff0034 with a measure

 

I my Month column as a date column, I've tried using all kinds of features including, calc, sum, sumx, time based functions.

 

The commonly reccomended filter of [Date] <= MAX [Date] always returns an error. The Earlier function returns errors with concerns there isn't a function above it. Any help would be greatly appreciated; I've spent hours today trying to work this out and I get the feeling there is a difference in Pivottable dax and powerbi dax. I want to be able to shape and transform my data in powerbi, using dax in powerbi.

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@ElliotP Sorry about the original post. It was from my phone and had typos Smiley Wink

 

Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)

 

Running Total COLUMN =
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
)

 

And as you can see it works! Smiley Happy

 

Running Total 2.png

 

And here's the MEASURE formula

 

Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

 

Which also works...

 

Running Total 3.png

View solution in original post

55 REPLIES 55
Anonymous
Not applicable

Hi There,

I tried as suggested but only get it working almost.

pfabra_0-1614130420025.png

 

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?

 

Actuals Cumulative =
CALCULATE(
SUM('Contract Revenue Forecast'[Actual]),
FILTER(
ALL('Contract Revenue Forecast'),
'Contract Revenue Forecast'[Month] <= EARLIER( 'Contract Revenue Forecast'[Month])
))

 

 

Thanks in advance!

 

Cheers,

BrentonC
Helper I
Helper I

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

 

 
 
running total.PNG
ajj263
New Member

Anyone know how to get cumulative total by site?

Date_TimeSitenumber
1/01/2015 6:00AA35
1/01/2015 6:00BB22
1/01/2015 6:00CC47
1/01/2015 18:00AA12
1/01/2015 18:00BB65
1/01/2015 18:00CC24
2/01/2015 6:00AA35
2/01/2015 6:00BB78
2/01/2015 6:00CC65
2/01/2015 18:00AA12
2/01/2015 18:00BB45
2/01/2015 18:00CC68
3/01/2015 6:00AA66
3/01/2015 6:00BB74
3/01/2015 6:00CC32
3/01/2015 18:00AA36
3/01/2015 18:00BB54
3/01/2015 18:00CC69
4/01/2015 6:00AA24
4/01/2015 6:00BB56
4/01/2015 6:00CC35
4/01/2015 18:00AA45
4/01/2015 18:00BB39
4/01/2015 18:00CC57
5/01/2015 6:00AA78
5/01/2015 6:00BB35
5/01/2015 6:00CC15

@drnareshchauhan 

It's a bit hacky although you could create references of the table and filter into each site (If there were not too many) Use the solution supplied to accumulative sum based on the date. Then union all of the tables... although I am sure there must be a better way...
EDIT: 
I am ashamed I even thought of that, a much better way to sum on two columns in your situation is using Python

 

# 'dataset' holds the input data for this script
dataset['cumsum'] = dataset.groupby(['site','datepd'])['number'].cumsum()

 



Notes:

Pandas does not like returning dates properly, create a duplicate column which is date type datepd of your date column.
Change your original date to text so you have a reference of the date afterwards.
Use the code above.

Select the dataset.

Remove the datepd column

Change the date column to date type again.

SHould be good to go.



Capture.PNG

Thanks for solution. Could you please suggest how we can wite DAX / Measure formula to get cumulative sum like this.. 

Zhengy
Frequent Visitor

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

serh2
New Member

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. 

1.JPG2.JPG

wstan77
Regular Visitor

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.

 

Capture.PNG

 

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.

plaa
Frequent Visitor

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

 

 

ShokYee
New Member

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!

 

 

rdurkin
Advocate V
Advocate V

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

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.

 

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

Hi @rdurkin , I am not able to understand how the IF statement is handling the error? Please explain in a bit more clear way. Thanks.

Anonymous
Not applicable

Hi
I try the formula above but I can't get results, I don't know why. Can you help?
 
CALCULATE (
SUM ( vwExecucaoMensalItem[BudgetPurchaseValue] ),
FILTER (
ALL (vwExecucaoMensalItem[Date] ),
vwExecucaoMensalItem[Date] < MAX ( vwExecucaoMensalItem[Date] )
)
)
sdjensen
Solution Sage
Solution Sage

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

/sdjensen

@sdjensen I have read that article and that's the base I've been working off, yet to no avail. When I either attempt to impose the measure on a table or create a new column with that data it simply gives me the same value that corresponds with the data.

 

Photos to better demonstrate the issue:

https://gyazo.com/ca41ce0b2d8ec572608d4afda4cffd32

https://gyazo.com/4e8d9b3e1cc38c514048272ced01a534

https://gyazo.com/14a6089654df6e90e7fd5595fd842ebd

 

The Date colum is set to a date, the Unique Pageviews is set to Whole Numbers. I'm honestly lost at this point.

Vvelarde
Community Champion
Community Champion

@ElliotP

 

Replace this:

 

All(Allwebsitedata(2) [Date]),

 

By

 

All(Allwebsitedata(2)),

 

 

The reason is because you are using the date field in your data Table, if you'll use a calendar table the formula works perfects.




Lima - Peru

@Vvelarde

 

Thank you so much, we're making progress.

 

I've tried it as a measure as well as a new calculated column, yet it shows the cumulative total in each row; photos to demonstrate;

https://gyazo.com/0d365fcaaba2507bca2dffe1177837eb

https://gyazo.com/37b810f7b9f4659492b405b3362106db

 

As well, what do you mean in regards to date field date table. Should I set it to another table type?

Vvelarde
Community Champion
Community Champion

@ElliotP

 

For a calculated column :

 

CumulativeQuantity2 =
VAR CURRENTDATE='All Web Site Data (2) '[Date]
RETURN
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2)');'All Web Site Data (2) '[Date]<= CURRENTDATE))

 

cumu.png

 

For a measure:

 

CumulativeQuantity-M =
CALCULATE(SUM('All Web Site Data (2) '[UniquePagePreviews]);FILTER(all('All Web Site Data (2) ');'All Web Site Data (2) '[Date]<= MAX('All Web Site Data (2) '[Date])))

 

I hope this help you.




Lima - Peru
Sean
Community Champion
Community Champion

@ElliotP Sorry about the original post. It was from my phone and had typos Smiley Wink

 

Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)

 

Running Total COLUMN =
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
)

 

And as you can see it works! Smiley Happy

 

Running Total 2.png

 

And here's the MEASURE formula

 

Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

 

Which also works...

 

Running Total 3.png

View solution in original post

Hi, 

 

Thanks for this measure as it perfectly works. However it doesn't sync with any other filter options then. For example I have a slicer and want to filter on a specific area but the chart takes all the data instead of the slicer filter. I guess it's because we're doing an All filter option in the formula. How can I fix this so that my cumulative chart also syncs with my slicer?

 

Regards,

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors