cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Re: Cumulative Total

I am trying to calculate the running sum of a column but I am not getting it.

Please take a look at the formula and tell me where I am going wrong.

 

Cumulative = CALCULATE(SUM('dummy'[Column2]), FILTER(ALL(dummy),('dummy'[Column1])<=MAX('dummy'[Column1])))
 
Thanks,
Highlighted
Helper I
Helper I

Re: Cumulative Total

Hi @Anonymous 

 

Can you send us a pic of your data?

 

I assume Column1 is the date column, and Column2 has the number value?

 

Try to use ALLSELECTED instead of all.

Cumulative = CALCULATE(SUM('dummy'[Column2]), FILTER(ALLSELECTED(dummy),('dummy'[Column1])<=MAX('dummy'[Column1])))

 

You can also have a look at the TotalMTD, TotalQTD and TotalYTD DAX functions which work great for cumulative totals.

 

Dawid van Heerden

Twitter: @dawidvh

YouTube: https://www.youtube.com/davestechtips

**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Highlighted
Regular Visitor

Re: Cumulative Total

You saved my sanity--thank you!

Highlighted
Anonymous
Not applicable

Re: Cumulative Total

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] )
)
)
Highlighted
Frequent Visitor

Re: Cumulative Total

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

Highlighted
New Member

Re: Cumulative Total

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
Highlighted
Frequent Visitor

Re: Cumulative Total

Hi,

 

I am intersted in this method, I tried to apply but it didnt work.

Could you please help me with the following:

So I want a column to show me : 

0.63

1.34

2.08

Capture.PNG

Thank you in advance

Highlighted

Re: Cumulative Total

@Sean

Hi sir running total measure is workong in my case but it does not show differnt value for differnet categories .

I want to show a cumulated covid cases as per district but I cant do it I tried many formula sice two day 

someone help me

thanks in advance !

WHEN I USE MEASURE

 
 
date districtresultsno of confirmed       
20-5-20APOS1      
21-5ANEG0      
22-5APOS1      
23-5ANEG0      
24-5BPOS1      
25-5BPOS1      
26-5BPOS1      
27-5CPOS1      

 

Highlighted
Frequent Visitor

Re: Cumulative Total

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
Highlighted
Frequent Visitor

tRe: Cumulative Total

@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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors