One of the most powerful features of Power BI when it comes to DAX is a time series analysis. Using pre-defined DAX functions can allow you to report on values based on specific time periods. You can do things like referencing the previous year’s value, have a running year-to-date total, find closing balances for the month, and much much more.
For this blog, I want to explain how to get a rolling total for a specific time period. Rather than just simply writing the expression, I am going to break it down step by step of how it is built out and why it works. By understanding the reasoning behind the formulas, you can leverage this knowledge to apply time series analysis into your own Power BI reports.
For our rolling total, I will find the rolling sales amount for the last 3 months no matter what level of dates I am currently viewing in a visualization. This calculation can be easily modified to finding a rolling total for the last 14 days, 2 quarters, or whatever you see fit. You are also not limited to just doing totals, but any kind of mathematical aggregation is supported.
If you have not been introduced to using variables you will see them demonstrated here as an easier way to read and write your code. Also, discussed in this calculation apart from the time series DAX functions, will be calculate, filter, and all.
Check it out below:
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 our On-Demand Learning classes from https://pragmaticworks.com/pricing/