How to aggregate the "same" month data from different years (Seasonality Calculation)
We have a scenario which is to calculate the seasonality % based on the past 3 years' actual, and the seasonality is applied for the planing of the next four years equally. For example:
FY18, January (Seasonality %) = FY19, January (Seasonality %) = FY20, january (Seasonality %)
= ( FY14, January + FY 15, January + FY16, January ) / FY14 + FY15 + FY16
And another requirement is that users can exclude certain year (e.g. exclude FY 15 due to irregular data) for the seasonality calculation.
I found it very difficult because I couldn't find any functions to addup all the same month from different years and also define from/to (3 years ago to last year). I implemented it by using functions like LAG, TIMESUM, and adding lots of lineitems, but not easy for maintainance. Is there any easy method or idea to achieve this?
Thanks in advance
I attached the calculation image, please refer to it.