What is the cleanest formula structure to calculate a Year-over-Year (YoY) moving average across a custom rolling 12-month calendar block?
Solved
Modeling YoY Rolling Averages: Single metric vs. multi-step blocks?
- Apprentice Author
Best answer by Min Li
You can break the logic into 2 metrics:
Metric_1 = MOVINGAVERAGE(‘Source Metric’,12) //Calculate Rolling 12 Months Moving Average
Metric_2 = (Metric_1/Metric_1[select:Month-12]-1 //Calculate YoY Growth
+14- Master Helper
- Answer
You can break the logic into 2 metrics:
Metric_1 = MOVINGAVERAGE(‘Source Metric’,12) //Calculate Rolling 12 Months Moving Average
Metric_2 = (Metric_1/Metric_1[select:Month-12]-1 //Calculate YoY Growth
- New Here
Min Li’s two-metric approach is the right structure. Two things to watch when you implement it. First, MOVINGAVERAGE over 12 periods will give you partial-window averages for the first 11 months of your history, so if you want a true trailing-12 you’ll want to blank those out (a COUNT-based check or a simple month-index condition). Second, wrap the YoY in an IF to handle a zero or blank prior-year average, otherwise you get division errors showing up in boards. Keeping the two steps as separate metrics is also the better call for performance and auditability, since the moving average can be reused by other reports without recomputing it.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.