Hello Community,
I am trying to calculate the 3 year rolling average for the table below in Alteryx. The goal is to create a visual in PBI.
The data output is below:
Logic:
Average of every 3 year group
i.e. 2015-2017 average = (4+3+2)/3 = 3
2016-2018 average = (3+2+5)/3=3
2017-2019 average = (2+5+5)/3= 4
and so on..
expected result is "3 year rolling avg" column below
| Year | Total Programs | 3 Year Rolling Avg |
| 2015-01-01 | 4 | |
| 2016-01-01 | 3 | |
| 2017-01-01 | 2 | 3 |
| 2018-01-01 | 5 | 3 |
| 2019-01-01 | 5 | 4 |
| 2020-01-01 | 2 | 4 |
| 2021-01-01 | 6 | 4 |
| 2022-01-01 | 10 | 6 |
| 2023-01-01 | 5 | 7 |
| 2024-01-01 | 3 | 6 |
| 2025-01-01 | 7 | 5 |
| 2026-01-01 | 7 | 6 |
| 2027-01-01 | 4 | 6 |
| 2028-01-01 | 5 | 5 |
