Calculating Index past 3/6/12 months accross years

PXM.xlsx

Dear All,

I'm looking for some brilliant help as I'm stuck with an exercise that I need to do in a pivot table.

I attached a file to help you visualize my issue. What I'm trying to achieve is to calculate some Index Year Ago for the past 3 months, 6 months, 12 months. You can see in column R to W how I would need to see it (but in a pivot format). This file is just to give you an idea as in my case I have 20 countries, 15 brands, many other criterias and around 100K lines

The different problems I'm facing are:
- My P3 months are also part of my P6 months and P12 months. When I create a pivot to calculate my Index (logic of the formula written in Column O and P) it's not working as I have my different years in different column which have no value.
- My P6 calculation would be actually only month 4, 5 and 6. I would need to sum P3 + P6 to have the right figures (same logic for P12).
- If I have everything under only one column, my P3 becomes the sum of 2015 + 2016 which is not what I want to see.
- If I call the 2015 month something like P3 Year ago, then again in the pivot it's not working as it will do the sum of empty column.

I tried to turn this problem upside down but no matter with which approach I tackle it I always get stucked somewhere.

I acknowledge that this might not be crystal clear but I tried to summarize it to my best. Hopefully somebody will be able to help me here.
Thanks in advance to everybody.

Hi
See whether such an arrangement is good

Hi Stasinek,

thanks for your input. I used some the logic and finally got to my own methodology.
Thanks for spending some time on it.