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

71 REPLIES 71
Jaikoshta
Regular Visitor

Hello All,

 

Can someone please help me to calculate the running total please ?

I've tried several methods but unfortunately without luck 😞

 

I've an table with following columns:

  • issue/ defect reported date
  • Defect ID
  • Repaired (Y/N)
  • defect closure date

Jaikoshta_0-1652797649743.png

 

Jaikoshta_1-1652797689954.png

 

I would really appreciate, if anyone can help me on this.

 

Regards,

Jai

 

Hi Jai,

Can I suggest you try using the TOTALYTD function?

As you can see in my example below, it's quite straight forward for Sum functions so it should be quite straight forward for your count functions too. It also gives you the ability to set the year end date so that it groups the correct months together. That is the "31/3" in my example string below:

Order Intake Margin YTD Current Month = TOTALYTD([Total Order Intake Margin],CALENDAR[Report Date],"31/3")

Thanks a lot, finally it worked 😀

I've another measure to created with running total. For this, I've following columns:

  • Issue repair status (Y/N)
  • Issue closed date (available only if above column is "Y" otherwise blank)

And for this, I would like to create a measure, to show the cumulative based on the month (i.e. how many issues has been closed on which month)

Can you please help me this as well ?

Regards,

Jai

LoreGaggio
Frequent Visitor

CUMULATIVE SUM, date sensitive, solved at least for me:

 

BEWARE, all solutions provided before do not work if the dataset goes more in the past compared to the starting date you want to see in the Cumulative sum. Meaning, the solutions provided so far do not work if i want a Cumulative sum for Year-to-date 2022, if i have data for 2021, 2020... And i want the cumulative sum to adjust based on the year.

 

HERE A SOLUTION THAT WORKED FOR ME:

See the outcome pics below, it works even when changing year, only starting the Cumulative Sum from the beginning of the selected year!

 

The measure is essentially a conditional calculation between the first day of the selected year and the last day of the current period (e.g. month):

 

Nb of ship-to Cumulative Total =
IF([Nb of ship-to]<>0,
CALCULATE([Nb of ship-to],
DATESBETWEEN('Calendar'[Date], DATE(YEAR(MIN('Calendar'[Date])),1,1), 'Calendar'[LastDate])), "")

 

The IF function is only there to make sure i see blank cells in the months Year-to-go.

The LastDate measure is calculated as:

LastDate = Lastdate('Calendar'[Date])

 

LoreGaggio_0-1652174296105.pngLoreGaggio_1-1652174306342.png

 

rakesh_chaudhar
Frequent Visitor

Hi,

wouuld you like to solve my issue . I triying to get the cumulative flag according to %age.

one person have 4 channel if there 3 chanel fill 80% then previous three should be come in flag like "top 80%" and 4th should be "rest")

Hi its my humble request please solve this query

NameRetailerChannelValContriNEED ANS LIKE
AchampetR2C14236%Top 80%
AchampetR1C23026%Top 80%
AchampetR3C12622%Top 80%
AchampetR4C21916%Rest
ADDANKIR7C14538%Top 80%
ADDANKIR8C22622%Top 80%
ADDANKIR5C12118%Top 80%
ADDANKIR9C21311%Rest
ADDANKIR6C11210%Rest
ADILABADR11C27535%Top 80%
ADILABADR10C14421%Top 80%
ADILABADR13C23315%Top 80%
ADILABADR14C13115%Top 80%
ADILABADR12C23014%Rest
rakesh_chaudhar
Frequent Visitor

I Need help

for my question: IF my contri will completed 80% till any point then we have to make the 80% for each and every before meeted point : example given as " NEEd Ans like"

Please resolve my problem

 

NameRetailerChannelValContriNEED ANS LIKE
AchampetR2C14236%Top 80%
AchampetR1C23026%Top 80%
AchampetR3C12622%Top 80%
AchampetR4C21916%Rest
ADDANKIR7C14538%Top 80%
ADDANKIR8C22622%Top 80%
ADDANKIR5C12118%Top 80%
ADDANKIR9C21311%Rest
ADDANKIR6C11210%Rest
ADILABADR11C27535%Top 80%
ADILABADR10C14421%Top 80%
ADILABADR13C23315%Top 80%
ADILABADR14C13115%Top 80%
ADILABADR12C23014%Rest
IDKFA
Helper I
Helper I

Hi Legends!

Can I get some help with this please? As far as I can tell I've done exactly the right things to copy paste the solution but I can't get the correct result. Thanks in advance. 

IDKFA_0-1644469486426.png

 

E014945
Regular Visitor

Hi All,

 

Thanks for the previous answers to the initial poster. However, after reading all the posts on the subject i cannot find an answer to the following request:

 

I need to calculate a running total for QuantityBatches by Plan_Type, Product_ID and Calendar_date in order to make some stock projection and all of this sorted by the Calendar_Date.

 

Colonne =

    calculate(

        sum(View_Latest_Schedule[QuantityBatches]),

        filter(

            ALL(View_Latest_Schedule[Plan_Type]),

            View_Latest_Schedule[Plan_Type] = "Frac_Plan"

        )

        View_Latest_Schedule[CalendarDate] <= EARLIER(View_Latest_Schedule[CalendarDate] ) 

    )

 

 

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!

 

 

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

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 @Anonymous , I am not able to understand how the IF statement is handling the error? Please explain in a bit more clear way. Thanks.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors