Post reply

SSAS Calendar Week commencing with split at month

  • September 1, 2010 at 4:09 am

    #237502

    Hi all,

    Wonder if someone can help.

    Our CFO wants to see a cube calendar that shows week commencing but stops at the end of month.

    For example if the dates are

    Monday28th

    Tuesday29th

    Wednesday30th

    Thursday1st

    Friday2nd

    Saturday3rd

    Sunday 4th

    So for this example the week commencing is the 28th of Month 1 and Thursday is week commencing 1st of the next.

    I am having a problem seeing how I can do this.

    If I add the weeks to a standard calendar I get the week commencing 28th which includes the 7 days, naturally.

    I cannot help feeling I am missing something obvious.

    Cheers

  • September 1, 2010 at 7:23 am

    #1214939

    I think this might give some odd results when browsing the cube. However, it should be do-able.

    I would probably keep a natural calendar week as part of the dimension, where weeks always start on Monday(?) and end on Sunday(?).

    If I understand your statement you want to end up with partial weeks, not start the weeks on a different day of the week when the months transition. I would probably do something like a week of month column and a start date for week of month column. The week of month would be populated with 1, 2, 3, 4, 5. The start date for the week of the month column would be populated with something like 1-Sep-10, 6-Sep-10, 13-Sep-10, 20-Sep-10 and 27-Sep-10. I would probably also include, depending on your needs, a column that indicates the number of days in that month week. So the first week of September would have 5 as the number of days in the month week #1.

    Hope this helps.

  • September 1, 2010 at 7:35 am

    #1214949

    Thanks for that.

    I see where you are going. This is a new dimension table for the calendar you specifiy.

    Or I could add columns to our existing calendar dimension.

    Cheers.

  • September 1, 2010 at 9:03 am

    #1215062

    I would probably add to the existing dimension.

  • September 1, 2010 at 9:14 am

    #1215078

    Thanks all.

    Adding to the existing one is what I am testing now.

  • May 8, 2013 at 1:49 am

    #1613226

    I am very new to BI and also having the same requirement of partial weeks between months.

    My query is how you calculate Pervious Year MTD and Previous Year YTD with such Date dimensions?

    Thanks in advance!

  • May 9, 2013 at 8:45 am

    #1613838

    So the weeks can have anywhere from 1 to 7 days in them?

    How is that useful?

    "oh look our revenue this week is 7 times last week, good job team!".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply