cancel
Showing results 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

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
Community Champion

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

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!

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

80 REPLIES 80
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!

Anonymous
Not applicable

@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

Helper I

Hi @Anonymous , 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] )
)
)
Solution Sage

/sdjensen
Post Prodigy

@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.

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
Post Prodigy

@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?

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

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

Lima - Peru
Community Champion

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

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!

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

Helper III

Hi @Sean ,

I am also trying to create a cumulative total of the past dates and i have two conditions :

1) if delivery date <=Today() then it should populate the cumulative total of Quantity column untill today's date .

2) If delivery date >Today then it should give me only the value which is present in the delivery column.
I have tried creating Measure but its giving me Cumulative total of whole data however my expected result is below :
Measure 1

Sales YD = CALCULATE([sum_of_MGO1],
FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
ZIBP_PO_MGRTN[DELIVERY_DATE]<=MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

My desrire result should be like this :

I have tried couple of more meaure but it has Date column and i am not able to use any min, max, selectedvalue while taking date and due to that my If condition is not wokring :
Measure 2(Not working) but when i created Column using below formula its calculating the Sum of whole  Column(MNGO1)

check_2_YTD =
VAR beforeToday =
CALCULATE(
[sum_of_MGO1],
FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
ZIBP_PO_MGRTN[DELIVERY_DATE] <= MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

VAR afterToday =
CALCULATE(
[sum_of_MGO1],
FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
ZIBP_PO_MGRTN[DELIVERY_DATE] > MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

RETURN
IF(ZIBP_PO_MGRTN[DELIVERY_DATE]<=TODAY(),beforeToday,afterToday)

I really appreicate if you can help me out in getting this result .

Thanks,

Ashish

New Member

Hi,

I have similar data but instead of dates , I have year column and I have first two rows which I want to exclude in my cumulative total bcos first row has value but no year and then year starts from 2002 till 2023. I want to ignore 2002 and start cumulative total from 2003 onwards,

Year sales  cumulative

10.              -

2002.  20.           -

2003.  30.          20

2005.  50.          70

2006   10.          80

is this possible and how, my year is from calendar table

Regular Visitor

Hey Sean, this works great and was able to create a measure along these lines. However, I am running into an issue where the cumulative value is null since there are no records for that week for the given person. I'd assume that even if its null, it should just repeat the previous week's value in the next week. How to handle this, this causes an issue particularly for a line chart on the weekly level.

Frequent Visitor

I had trouble with this formula, and the issue was the ALL() statement required my date column again. Shout out to this website for showing me.

https://powerbidocs.com/2020/11/08/cumulative-total-running-total-in-power-bi/

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

Helper III

Thanks, I needed this!

Anonymous
Not applicable

Is there a way to do this that keeps any filters applied to the original table? For example, if there was another filter called 'PageViewsFromCountry', for which we would like to apply a filter or slicer visual. I have tried using keep filters with no success:

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

Edit: For anyone at this stage of their DAX journey, simply ommitting any function under FILTER in CALCULATE allows for the addition of filter conditions, preserving current context. So the cumulative total can be calculated with respect to any filters already applied to the table by:

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

Regular Visitor

Doesn't work in the line chart if there are 0 values for some periods

Regular Visitor

I've followed an identical approach as you have provided here but run into this issue when it comes to the current year context and the previous year calculating the full total as opposed to just the four months of this year.

Frequent Visitor

Make sure the code you are using has ALLSELECTED rather than just ALL in the filter part of the expression. This should then reflect the various slicers that you have employed on the page.

Regards

Neil

Regular Visitor
Yep, I have that part in already

App Cumulative =
CALCULATE(
[# Applications],
FILTER(
ALLSELECTED( 'Applications' ),
'Applications'[APM_CREATED_DATE] <= MAX(Applications[APM_CREATED_DATE])
)
)
Frequent Visitor
Hi @seancasey, here is an example I am using that works for all filter selections on related tables. I am doing my date segmentation based on a calendar table.

CumulativeInvoiceQty =
CALCULATE
SUM ( FACT_SalesOrderLines[InvoicedSOQty] ),
FILTER
(
ALLSELECTED ( 'DATE_TABLE' ),
('DATE_TABLE'[Date]) <= MAX ( DATE_TABLE[Date] )
)
)