Forum Discussion
Help with cumulative total
I've looked at many, many posts for how to calculate a cumulative/running total and not a single solution worked for me.
I have a table that looks like this:
I want to calculate the running total of "TotalVacc" for each flu season. So each month within each season should show a cumulative number of doses, which then resets for the next flu season.
Please help!
afrerichs update the measure as below:
RT Measure = CALCULATE ( SUM ( 'Monthly Flu Admin'[TotalVacc] ), FILTER( ALL ( 'Date Table'[Date] ), 'Date Table'[Date] <= MAX ( 'Date Table'[Date] ) ), VALUES ( 'Date Table'[Flu Season] ) )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
13 Replies
- parry2k
Super User
afrerichs what measure you have tried so far?
You should be using date dimension in your model and then it will be super easy with the measure below:
RT = CALCULATE ( SUM ( Table[Vacc Column] ), FILTER( ALL ( 'Date Table'[Date] ), 'Date Table'[Date] <= MAX ( 'Date Table'[Date] ) ) )check the playlist on my YT channel about the importance of date dimension in your model https://youtube.com/playlist?list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Thank you, I have tried something similar! Here's what happens when I use your DAX calculation:
The RT column isn't actually a running total.
The Date column I used is from a date table that looks like this:
What am I doing wrong?
- parry2k
afrerichs In visual, you have to use year/month column from the date table.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
This is what happens when I use the year & month columns from my date table:
Yes, there's a relationship between the two tables. How can I share a copy of the pbix?
parry2k Let me know if this works! https://drive.google.com/file/d/1n-tEcmKKw7-8lt0fqEu6ZkB36PxiXuCa/view?usp=sharing
- parry2k
afrerichs you added RT as a column, you need to add it as a measure
Result after adding it as a measure
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Thank you. I made it as a measure now; but how can I get it to "reset" for each flu season?
- parry2k
afrerichs update the measure as below:
RT Measure = CALCULATE ( SUM ( 'Monthly Flu Admin'[TotalVacc] ), FILTER( ALL ( 'Date Table'[Date] ), 'Date Table'[Date] <= MAX ( 'Date Table'[Date] ) ), VALUES ( 'Date Table'[Flu Season] ) )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
