I didn't fully understand your setup, so I designed a sheet to show how I would proceed.
On sheet comparision, you select store and period. after that, you can use autofilter to get sales for compared weeks/months.
It looks like all your date groupings are on weekly base. So I did likely, but in my table, I used ISO week system (you can always edit formulas to change this):
1. The week starts at Monday;
2. The week belongs into year, where it's Thursday belongs to.
I added the rule:
The week belongs into month, where it's Thursday belongs to.
There was the problem with store opening date - there was nowhere said, what it is. In my table, I did go with 1st sales date in your Table1. The dates in comparison table are set active for comparison, when the store was active and the date was earlier than today in both years.
|