Drill down - weeks

[Anonymous]

I need to include weeks into the date hierarchy. So I created my custom hierarchy based on a date table, where year = Year(DateTable[Date]), month = Month(DateTable[Date]) and Week = WEEKNUM(DateTable[Date];21) (i used 21 because of European week system). But when I drill down into a hierarchy on a column chart, I get problems at the week level, because some weeks are split between 2 months, so then the same shows up on the visualization (e.g. Feb w9 and Mar w9).

[Oxenskiold · Accepted solution]

Hi @Anonymous,

You'll have to decide on a rule for the naming of the week number string that categorizes the individual dates so the dates of a given week will always belong to only one week number category.

Example of rule:

"The Year in the week-number-string is for any given date the year belonging to the Monday of the week of a given date and the month name in the week-number-string is for any given date the month name belonging to the Monday of the week of a given date and the week number of the week-number-string is that of this Monday"

Here is an example using the above (somewhat verbose) rule:

weeknostr =
FORMAT (
    YEAR ( Datetable[Date] - ( WEEKDAY ( Datetable[Date]; 2 ) - 1 ) );
    "0000"
)
    & "-"
    & SWITCH (
        MONTH ( Datetable[Date] - ( WEEKDAY ( Datetable[Date]; 2 ) - 1 ) );
        1; "Jan-";
        2; "Feb-";
        3; "Mar-";
        4; "Apr-";
        5; "May-";
        6; "Jun-";
        7; "Jul-";
        8; "Aug-";
        9; "Sep-";
        10; "Oct-";
        11; "Nov-";
        12; "Dec-"
    )
    & FORMAT (
        WEEKNUM ( Datetable[Date] - ( WEEKDAY ( Datetable[Date]; 2 ) - 1 ); 21 );
        "00"
    )

It's important that you have the year included because situations could occur where the first date(s) of a year will belong to week dec-52 and so will some of the last dates. (the year 2012 is an example of this)

It might be necessary to exclude the month from the date hierarchy to make it work depending on the way you drill down. I know the september update has changed the way you can drill down so try both methods. It's not possible for me to fully test the measure and the different way to drill down so you will have to try it as is.

Best regards

Oxenskiold

[Anonymous]

Thank you for the solution @Oxenskiold. I was hoping there would be an easier way to do this. But now I can implement your solution.