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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

80 REPLIES 80
Syndicate_Admin
Administrator
Administrator

Hello, I would like to know what formula you have applied to reach that conclusion. I am in a similar situation where I want each reference to deduct from its stock the sales orders by order lines, for this I have to take for each order line the accumulated available stock.

Anonymous
Not applicable

I FINALLY FIGURED OUT HOW TO DO THIS FOR A COLUMN OF STRINGS

There's definitely a better way but I started by making a calculated column which converts my strings to numbers. In the example below, I was counting all parts that were not "good"

 

Calculated Column:

converter column = IF('table'[quality] == "Good", 0, 1)

Running Total =
CALCULATE (
    SUM ('table'[converter column] ),
    ALLEXCEPT ('table', table[converter column]),
    'table'[Tool Time] <= EARLIER ( 'table'[Tool Time] )
)
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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")
Anonymous
Not applicable

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
Advocate I
Advocate I

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

 

Hello!

Something similar happens to me, I am looking for a function that shows me the accumulated per month for 2023 taking into account the values of 2020, 2021 and 2022, as follows:

aranza_mm_0-1677514664563.png

But I do not manage to make January start with the accumulated values of previous years and that from there it accumulates

Will you know how?

By the way, I found the function of "IF([]),<>0, "") very useful. Thank you!!

Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Advocate I
Advocate I

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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