How to Create Rolling Totals and Rolling Averages in Power BI
- Bernard Kilonzo
- Jan 24
- 2 min read
What is a Rolling Total and a Rolling Average?
A rolling total calculates the cumulative sum of a measure over a defined period. Unlike a simple cumulative total that always starts from the first date in your dataset, a rolling total looks back over a specific window - for example:
Last 7 days
Last 30 days
Last 3 months
Last 12 months
A rolling average on the other hand is the average of a measure over a moving window of time.
It uses the same idea as a rolling total, but instead of summing values, it averages them.
Both rolling total and rolling average are helpful in smoothening out noise and volatility - making trends and patterns easier to see.
Step-by-Step Guide
Step 1: Build a Proper Date Table
Before any rolling logic, you need a dedicated, continuous date table related to your fact table on a date column.
I have shared in a different article how to create a date table in Power BI.
(In this example, I have created my date table ranging from January 1st, 2017, to December 31st, 2020, just like my fact table)
Step 2: Create Rolling Totals (Running Totals)
Rolling totals calculates the cumulative sum of a measure over a defined period as shown below.
Example 1: Rolling 7 Day Total
Example 2: Rolling 30 Day Total
Example 3: Rolling 12 Month Total
Step 3: Create Rolling Averages (Moving Averages)
Rolling average computes the average of a measure over a defined period smoothening out noise and volatility.
Example 1: Rolling 7 Day Average
Example 2: Rolling 3 Month Average
Step 4: Visualizing Rolling Metrics
The above rolling calculations can be visualized to create the views below.
Rolling Totals
Rolling Averages
Conclusion
Rolling totals and rolling averages are two of the most valuable analytical patterns you can add to your Power BI toolkit. They transform raw, day‑to‑day fluctuations into meaningful trends, helping you understand momentum, seasonality, and long‑term performance with far greater clarity. By combining a well‑structured Date table with time‑intelligence functions like CALCULATE, DATESINPERIOD, AVERAGEX, and LASTDATE, you gain the ability to build dynamic, context‑aware measures that respond instantly to slicers, filters, and user interactions.
Whether you're analyzing sales, forecasting demand, monitoring operational metrics, or smoothing noisy data, rolling calculations give your reports a level of depth and insight that static summaries simply can’t match. As you continue building dashboards, consider where rolling windows can reveal hidden patterns or provide a more stable view of performance. With these techniques in hand, you’re well‑equipped to create richer, more insightful Power BI reports that help your audience make smarter, data‑driven decisions.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!!