Recently after a private training, I had a follow-up email from one of the participants. He asked if there was a way to return a running total for a column of values. I immediately went to using a DAX function called YTD (Year To Date) which when you reference an expression and feed the function your column of dates from your date table, the formula will continually add your numbers through time until you reach the end of the year. After sharing my solution, he asked if there was a way to do this without having it reset at the end of the year. He wanted a rolling total for all time and not just a year. So I had to change my approach.
In order to achieve his solution, I made a calculated column, but it wasn't just a basic column. I had to override the row-level context that is present when running calculated columns. I needed to return the values for the current date of the row and any previous dates where the value is present. Also, it needed to run for specific agency values which he had many of. In order to accomplish this, I used variables as well as the Calculate function. Take a look below to see the whole solution.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI, Power Apps, Power Automate, Copilot Studio, Fabric, Azure and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks, along with fresh content every week.
It's also incredibly easy to get started with your 7-day free trial. No credit card required. Just click below and you'll be on your way.
(904) 638-5743
-1.png)
Leave a comment