Cumulative Total

[Anonymous]

I am trying to create a visual which shows the cumulative total. I have tried creating a quick measure using running total, but this is the result I get:

My base value is the count, and the field is the month. It seems to be calculating backwards for some reason. I have tried amending the formula as well, but nothing I do seems to change the results.

This is the desired result:

Any suggestions as to what is wrong with my measure or what formula I can use to achieve this?

[TheoC · Accepted solution]

Hi @Anonymous 

If it's calculating backwards, you may need to check that your "Month" column is formatted as "Month Year".  Once you do that, you can use a measure like below:

Measure = TOTALYTD ( SUM ( Table[Column] ) , Table[Date] )

This is the tabular view:

Hope this helps!

Theo

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple smaple, please refer to it to see if it helps you.

Add an index in Power Query.

Create a measure.

Measure = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index])))

Or you can use 'table'[date]<=selectedvalue('table'[date]) replace the index part.

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thennarasu_R]

Hi,
Try this one It will help to you Your scenarios,

Thanks,
Thennarasu R

[Anonymous]

Hello,

I have tried your solution, unfortunately it seems to be adding my previous results (refer below) to the column (latest result)

Previous result:

Latest result:

[TheoC · Accepted solution]

Hi @Anonymous 

If it's calculating backwards, you may need to check that your "Month" column is formatted as "Month Year".  Once you do that, you can use a measure like below:

Measure = TOTALYTD ( SUM ( Table[Column] ) , Table[Date] )

This is the tabular view:

Hope this helps!

Theo

[Anonymous]

Hello Theo,

Thank you for helping me out with this. However, I am still facing the same issue. 

This is how I've amended the formula: 

**bleep** New Cust = TOTALYTD ( COUNTA(Consolidated[New Customer]), Consolidated[Cust Join Date])

My date column is formatted in month year format, but my results remain the same.

Also, if it matters, I am trying to count the number of new customers, and while SUM does not work, COUNTA seems to produce some form of result, even if not in the correct format. I'm still not very sure what is wrong my workings. I really appreciate you helping me through this.

[TheoC]

@Anonymous did you try the other option I provided?

I'd recommend changing your COUNT to a measure like Count Customers = COUNT ( Consolidated[New Customer] )

From there, try either:

1.  Measure = TOTALYTD ( [Count Customers] ) , Table[Date] )

or

2. CALCULATE ( [Count Customers] ) , 'Table'[Date] <= MIN ( 'Table'[Date] ) )

Of those don't work, it might be best you provide some dummy data.

Best of luck!

Theo

[Anonymous]

Hello Theo,

I did give your other solution a try, but no matter what I do, my results remain unchanged.

I'm thinking that the date seems to be the issue here. The date column was obtained using the following codes:

= Table.AddColumn(#"Added New Cust in FY", "Cust Join Date", each if List.Contains({"95", "96", "97", "98", "99"}, Text.Start([CustID],2))
then Text.Middle([CustID],2,2) & "/" & "19" & Text.Start([CustID],2)
else Text.Middle([CustID],2,2) & "/" & "20" & Text.Start([CustID],2))

The date was derieved by concatenating the first 2 digits of a text column along with other texts, then changing the data type to date after the column was created. I experienced no issue while doing so. Perhaps you could identify an issue that I am unable to see regarding my date column? Thank you!

[TheoC]

@Anonymous just in case you have complications with the above, another way of calculating cumulatives in this scenario is with the following measure:

Measure 2 = CALCULATE ( SUM ( 'Table'[Count] ) , 'Table'[Date] <= MIN ( 'Table'[Date] ) )

Output is per below:

Hope this helps!

Theo 🙂