custom calendar to switch between customer service month and month year
i want to create a calendar that can relate values based on the selection of either service month by customer (e.g. compare values for service months 1-6 between customers) and month year according to a normal calendar (compare values for Q1 2013 for all customers). my desired output is attached. does anyone have any ideas on how i can script this in the load where i would provide the service period starts for all customers along with corresponding month year?
For your service month, specific to a customer, you could find the service start (min Period or a service start date per customer from a separate table), and create a table in your model with fields Customer and ServiceStart.
It might also be good to in addition create a consecutive MonthID, a number that counts the months from a certain point in time (say, January 2000), so we are now in month with MonthID, hmmm, 14*12+3=171.
Then join the ServiceStart to your fact table and create a difference between ServiceStart and current MonthID for your ServiceMonth.