I have the following situation in my cube:
Store A uses the Cal1 calendar. Their sale month begins on January 5th. Store B uses the Cal2 calendar. Their sale month begins on January 10th. Shop C ... etc.
A calendar store cannot simply be represented as offsets of the main calendar. They have different working days, holidays, etc.
I need to prepare a daily report (reporting services) with the actual calendar date as a parameter. The list of stores is also a multi select parameter. If the user selects January 15, I need to show combined MTD sales for all the stores selected in the parameters. Thus, this would mean the first 10 days of sales for store A and the first 5 days of sales for store B, etc.
Any ideas how I can make this work? I will also need to provide YTD numbers in the same way.
I execute several calendars using a bridge table between my dates and calendar sizes. This is the method described here: http://duncansutcliffe.wordpress.com/2010/06/11/a-better-date-dimension/
I can’t hardcode the calendars, as there is a requirement, maybe add more in the future without changing the scheme.