I am working on a cube that uses the 4-4-5 accounting method. I have my time dimension setup and working properly. The problem I have is that every 5 years there are 53 weeks in the fiscal year. Can someone help me figure out how to handle comparable sales when one year has 52 weeks and another has 53 weeks.
Year 2010 has 52 weeks
Year 2009 has 53 weeks
When comparing weekly (daily, quarterly, etc.) sales of 2010 with 2009 the calendar days offset by one week.
Week 1 of 2009 is Dec 29 2008 - Jan 4 2009
Week 1 of 2010 is Jan 4 2010 - Jan 10 2010
However, when I compare week 1 2010 with week 1 2009 I need to compare it with week 2 of 2009 (Jan 5 2009 - Jan 11 2009) because of the 53 weeks in fiscal year 2009. Currently my calculated member compares week 1 2010 with week 1 2010. Normally this would be great, but in the case of a 53 week year.
If possible, I would like this to built into the cube by modifying my time dimension so complicated MDX doesn't have to be written for cube browsing.
Thank you for your ideas.