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.
If you enjoy this video or any of my other videos and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" to get 20% off at check out when purchasing any private training or On-Demand Learning classes from https://pragmaticworks.com/pricing
(904) 638-5743
-1.png)
Leave a comment