Performing MTD/YTD calcs over multiple calendars in analysis services

I have the following situation in my cube:

Shop A uses calendar Cal1. Their sales month starts Jan 5th. Shop B uses calendar Cal2. Their sales month starts Jan 10th. Shop C...etc

Shop calendars can not simply be represented as offsets of a main calendar. They have different working days, public holidays etc.

I need to produce a daily (reporting services) report with the actual calendar date as a parameter. The list of shops is also a multi select parameter. If a user selects the 15th of Jan, I need to show the combined MTD sales for all shops selected in the parameters. So that would mean the first 10 days of sales for shop A and the first 5 days of sa开发者_如何学编程les for shop B etc.

Any ideas how I can make this work? I'll also need to provide YTD figures in the same manner.

I am implementing multiple calendars using a bridging table between my date and calendar dimensions. It is the technique described here: http://duncansutcliffe.wordpress.com/2010/06/11/a-better-date-dimension/

I can not hard code the calendars as there is a requirement to possibly add more in the future without modifying the schema.

I am not sure I understand you sales data start days, but if I do then the solution is to make an extra dimension as a "reporting calendar" as a point of harmony between the actual calendars

Each shop has a known offset to the reporting calendar, so for shop A it's 5 days, for shop B it's 10 days etc

When you add fact data you also need to calculate a reporting date using the offset. So for Shop A 5 Jan is actually 1 Jan etc

When reporting, the user selects a date on the reporting calendar, and facts are selected based on that

e.g. if the user selected reporting calendar 15th Jan, it would only select actual dates 1 to 15 Jan and reporting calendar up to 15 and only Jan

Data selected would be Shop A 5 to 15, Shop B 10 to 15

1 to 4 Jan for Shop A and 1 to 9 for Shop B would be in Dec of the reporting calendar, and not included because of the filter of reporting calendar Jan