Cumulative Total

[ElliotP]

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.

[neilcotton]

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

)

)

[seancasey]

thanks @neilcotton , I think the lack of a date table is probaly what's causing me the trouble I'm having. Will add one now and see where it gets me.

[Tayyaba21]

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,

[Justas4478]

@Sean  Hi I tried using the measure and I cant get it to work. I dont have sales volumes in column format. I can only use Sales Volume as measure for the caluclation. Is there a way to make moeasure to work when using measure?

This is how the measure looks. Thank you.

Cumulative Sales volume =

CALCULATE (

SUM ( 'Actuals'[Sales Volume] ),

FILTER (

ALLSELECTED ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] )

))

[AliceW]

Thank you so much for the formula!

I made an adjustment so that various filters applied in the page would work: instead of ALL(), i've used ALLSELECTED([Date]).

[neilcotton]

Alice, you are a life saver. I've been trying to figure this out for 2 days.

Regards

Neil

[AliceW]

Happy to help, Neil!

[drnareshchauhan]

@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	district	results	no of confirmed
20-5-20	A	POS	1
21-5	A	NEG	0
22-5	A	POS	1
23-5	A	NEG	0
24-5	B	POS	1
25-5	B	POS	1
26-5	B	POS	1
27-5	C	POS	1

[tamim]

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

Thank you in advance

[Crunchy]

You saved my sanity--thank you!

[Anonymous]

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,

[dawidvh]

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.

[sofias]

Hello,

I've found your answer since I was struggling to build a cummulative column. However, I'm still struggling with the following:

1) RUNNING_NMV_FINAL  = MEASURE = 

IF(AND(ISFILTERED('dim pm'[payment]),ISFILTERED('DIM TIER'[TIER])),

CALCULATE (

SUM (Table2[NMV]),

FILTER(ALLEXCEPT(Table2,Table2[PM],Table2[TIER]),Table2[Index] <= MAX ( Table2[Index])

)

),

calculate(sum(Table2[NMV]),filter(all(Table2),Table2[Index] <= max(Table2[Index])))

)

If you notice, I have filters selected. And this measure only works when I have, either, one selection per filter or when no selection is done in ANY filter. 

2) Running nmv COLUMN  = column

CALCULATE (

SUM (Table2[NMV]),

ALL (Table2),

Table2[Index] <= EARLIER (Table2[Index])

) --> followed your logic

Although it is displaying the running total with no slicers selected, when I start to filter, it will not change dynamically as I need. 

In the end of this, what I would need to do is to divide each row of this cummulative_sales by the TOTAL_SALES_AMOUNT, which will be a %. Then I will do the same for the Losses (all the process before mentioned).

I need this because I want to create a line chart with 2 measures (cummulative sales for X axis and cummulative losses for Y axis) and on the legends I would put the country (so, 1  line per country). But the line chart does not allow me to put measures on the X axis, hence my need of using columns.

Can anyone help me please? I am starting to give up on this :(.

Thank you!!!

[POB]

The pattern for cumulative totals in measures seems pretty well established using the formulation "<= MAX("

From testing it out myself, it appears that the MAX function in this case evaluates in the Row Context. 

 

What I don't understand is why is does this rather than evaluate in the Filter Context, which the description seems to imply it will do:

[tameemyousaf]

@Sean What if we have multiple rows for same date?

[Anonymous]

Hello; I am trying to create a table just like this. I followed the DAX but could not go through. Can you help me with this?

I already have the formila but cannot get it right.

[koenmilt]

It looks good to me, try using ; instead of ,

[harrisonp78]

Hi,

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

P

[harrisonp78]

Hi,

I am trying to do something similar to this - a 12 month rolling sum of sales that I can then show by month (ie september'17 will show october'16-september'17, october'17 will show november'16 - october'17, etc). I have managed to get this to work by modifying the formula in post 2. However, when using this, no filters work on my data - If I try to filter by product, customer gender, sales office, the numbers do not move. I have tried numerous filter variations but simply cannot get this to work. Can anyone offer any suggestions?

P

[koenmilt]

Hi,

I tried the measure stated in the chosen solution (by Sean). This works!

My measure is as follows:

Cumulative Hours spend =
CALCULATE (
sum('OVERUREN_WEEK'[Hours Spend]);
FILTER (
ALL ('OVERUREN_WEEK'[Year_Week]);
'OVERUREN_WEEK'[Year_Week] <= MAX ( 'OVERUREN_WEEK'[Year_Week )
) )

In my report I want to have a table visual that has three attibutes/columns, being: Year_week, Cost_center and Employee.

With the current DAX, the cumulative kinda works, it is cumulative by year_week, and breaks by all other attributes in the table visual (so Cost_center and Employee).

However, I only want it to break by Employee, it should keep cumulate when an employee switches cost_center.

Example:

Employee  - cost center - function - year_week - hours spend - cumulative

Henk         -  2500           - Developer - 201701   - 3                   - 3

Henk         -  2500           - Developer - 201702   - 1                   - 4

Henk        -  4000            - Developer - 201703   - 2                   - 2

The cumulative restarts when the employee switches to a different Cost_center in week 201703.

The only way I was able to resolve was by removing Cost_Center from the table, But I dont want to do that.

[zq]

Hi there, I want to create a Running Difference instead of the Running Total (Sum) in POWER BI Table. Can you please guide how to achieve this?