Forum Discussion
Running Total solution for slicer
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
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
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.
😞
