Forum Discussion
Rolling average on quarterly data
Hi,
I am trying to calcualte a rolling average (or moving avergae) based on some quarterly data. I do not have days and dates, only sum for each quarter. The data table looks like this:
Quarter | Quantity |
Q1 2016 | 18650 |
Q2 2016 | 18565 |
Q3 2016 | 18262 |
Q4 2016 | 18453 |
Q1 2017 | 18283 |
Q2 2017 | 18260 |
Q3 2017 | 18086 |
Q4 2017 | 18127 |
Q1 2018 | 18363 |
Q2 2018 | 18374 |
Q3 2018 | 18261 |
Q4 2018 | 18307 |
Q1 2019 | 18473 |
Q2 2019 | 18573 |
Q3 2019 | 18429 |
Q4 2019 | 18577 |
Q1 2020 | 18763 |
Q2 2020 | 18641 |
Q3 2020 | 18276 |
Q4 2020 | 18370 |
What I am trying to accomplish is this;
That is, I am trying to calculate a 4-quarter moving / rolling average. The calculations are not hard, and I have shown examples for the two first values in the table above.
If anyone could offer som advice I would really appreciate it.
Thanks so much in advance.
Regards
Svein
Hi Sveina ,
If you don't have date column then you will need an index column.
And then create a column as below.
Column = IF('Table'[Index]>3,CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])-3&&'Table'[Index]<=EARLIER('Table'[Index])))/4,BLANK())Best Regards,
Jay
2 Replies
Hi Sveina ,
If you don't have date column then you will need an index column.
And then create a column as below.
Column = IF('Table'[Index]>3,CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])-3&&'Table'[Index]<=EARLIER('Table'[Index])))/4,BLANK())Best Regards,
Jay
Hi Jay,
Your solution worked absolutely great 👍😁
Thanks you so much for your time and effort, really appreciate it!!!!
Best Regards
Svein
