Running Total solution for slicer

[cpdanielmc21]

Hi community

I've been trying to apply solutions proposed in this forum for similar issues to mine, but have not been successful so far.

I want to create either a measure or a column that can help me visualize a running total that always calculates from the begining even when slider applied. Let me explain.

This is my table "data"

Month	Account	Date	Amount
1	101	1/15/2020	10
1	102	1/15/2020	4
1	101	1/31/2020	10
2	101	2/15/2020	15
2	102	2/15/2020	8
2	101	2/29/2020	15

And when I write a DAX measure like this:

Running Total = 
CALCULATE(
	SUM('data'[Amount]),
	FILTER(
		ALL('data'[Date]),
		ISONORAFTER('data'[Date], MAX('data'[Date]), DESC)
	)
)

I get this result (assume I filter account 101)

Month	Account	Date	Amount	Running Tot
1	101	1/15/2020	10	10
1	101	1/31/2020	10	20
2	101	2/15/2020	15	35
2	101	2/29/2020	15	50

And when I further filter Month 2, I expect to get the following:

Month	Account	Date	Amount	Running Tot
2	101	2/15/2020	15	35
2	101	2/29/2020	15	50

Note the first row is 35 because it includes the cumulated values from last month (20), or in other words, is including the opening balance that comes from January.

However the Running Total starts from 15 , instead of 35,

Any ideas for this?

I was thining of adding a new column that calculates the running total but, got the same.

[mahoneypat]

Please try this expression as a measure (replacing Table name "Total" with 'data').

RunningTotal =
VAR thisdate =
SELECTEDVALUE ( Total[Date] )
RETURN
CALCULATE (
SUM ( Total[Amount] ),
ALL ( Total ),
VALUES ( Total[Account] ),
Total[Date] <= thisdate
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[cpdanielmc21]

Hey @mahoneypat , Thanks!

This worked! Just want to ask if there is something else it can be done.

This only works fine if I filter a specific account, but when I want to show all accounts, it doesnt calculate the "global" running total, it is doing it by account.

This is what I see when I filter Month 2 and show all accounts. The running total starts fine on 35, but then on account 102 it starts again from zero. I would also like to see a sort of "global" running total ("Expected running total" down below)

Account	Month	Date	Amount	Running Total	Expected Running Total
101	2	2/15/2020	15	35	35
101	2	2/29/2020	15	50	50
102	2	2/15/2020	8	12	62

So, if I filter account 101 the running total should show 50 at the end (even if I filter Month 2 or if show all months), also, if I filter account 102, running total should be 12 (no matter what Month I filter or if there is no month filter) and If i show All accounts, it should calculate 62 at the end (even if I filter Month 2 or show all months).

If you got any idea for this, it would be great!

[mahoneypat]

Yes.  You can just take the VALUES( ) clause out of the CALCULATE().  You only had one account in the example data, so assumed (incorrectly) you would want it that way.

Regards,

Pat

[cpdanielmc21]

Hi @mahoneypat 

That's what I thought, so I erased that line, but get the following result (no filters applied):

Running total is weird now, and If suppose, I apply filter month "2" and no filer on account, running total should be 62, but shows 47 on last line. If i happen to also add filter Account "102", the running total now should be 12, but nope, it shows 47.

😞

[mahoneypat]

Try writing a new measure that references the original measure. This one will iterate over each account and add all of the running totals together. See if that works instead. Typing on a tablet. Sorry. 

New = Sumx(values(table[account]), [original  measure])

If not will look more later. 

Pat

[cpdanielmc21]

@mahoneypat 

Thanks Pat, but still getting same result, 😞

I just returned the original formula for running total and created the new measure, but it gives same numbers

NewMeasure = SUMX(VALUES('Table'[Account]),[RunningTotal])

RunningTotal =

VAR thisdate =

SELECTEDVALUE ( 'Table'[Date])

RETURN

CALCULATE (

SUM ( 'Table'[Amount]),

ALL('Table'),

VALUES('Table'[Account]),

'Table'[Date]<= thisdate

)

[mahoneypat]

The reason you are seeing the weird #s is because the max date for Account 102 is less than the max for Account 101.  You need to get the max date in the selected month in the variable first with this type of change.

RunningTotalAll = var thisdate = CALCULATE(MAX(Total[Date]), ALL(Total), VALUES(Total[Month]))

return CALCULATE(SUM(Total[Amount]), ALL(Total), Total[Date]<= thisdate)

Regards,

Pat

[cpdanielmc21]

@mahoneypat 

Hi Pat, thanks. I think is closer.

It shows 24 for Month 1 and 62 for Month 2, (indeed correct), but not showing the "development" of 10, 20, then 24, then 39, etc up until 62.

Also, if let's say I filter Account 102, should get 12, but get 62. In this case the original measure is the correct

[mahoneypat · Accepted solution]

Ok.  I played with this some more.  Please try this expression.  Having account in both the table and the slicer makes this a challenge. 

RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))

return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)

Regards,

Pat

[Tom2001]

That is a great measure.

RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))

return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)

But, I'm facing the situation when I'd need to have all accounts with balances even if they are not in the selection range "From ... To ...."
So, it would be 0 for the Amount but some Running Total that came from all previouse/before the selected dates range periods.
Could this measure be modified to do it?

[cpdanielmc21]

@mahoneypat 

Oh! This works!!

I just took out the account column from the table, just kept it in the slider and now all numbers show as expected.

Thank you so much Pat!

[amitchandak]

@mahoneypat ,

try like with a date table

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(allselected(date),date[date] <=max(table[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.