Power BI DAX: Running Totals Using Variables, CALCULATE, and FILTER

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/
Sign-up now and get instant access
ABOUT THE AUTHOR
Matt Peterson is a Trainer for Pragmatic Works specializing in the Power Platform. He graduated from the University of North Florida in 2006 and comes with 15 years of teaching experience in high school algebra. Matt earned the accomplishment in 2013 of being named the Florida Gifted Teacher of The Year. His primary focus is helping our customers learn the ins and outs of Power Apps and Power BI.
Free Trial
On-demand learning
private training
Leave a comment