Forum Discussion
Running Total solution for slicer
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.
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
12 Replies
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
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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!
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
