Date Custom Hierarchy to aggregate Year, Month then Start of Week

[jeanL]

Been trying to combine the visual for drill down where it will display aggregate according to Year, Month then StartofWeek - Given startofweek is Monday. 

VAR MaxValidToDate = MAX(WF_weekly[Ticket created - Date])
VAR MinValidToDate = MIN(WF_weekly[Ticket created - Date])
VAR _calendar =
CALENDAR(MinValidToDate, MaxValidToDate)
RETURN
ADDCOLUMNS(
_calendar,
"Year", YEAR([Date]),
"Week", "Week " & WEEKNUM([Date] - WEEKDAY([Date], 2) + 1, 2),
"WeekNumber", WEEKNUM([Date] - WEEKDAY([Date], 2) + 1, 2),

// Calculate the start and end of the week 
"StartOfWeek", [Date] - WEEKDAY([Date], 2) + 1,
"EndOfWeek", [Date] - WEEKDAY([Date], 2) + 7,

// Calculate date range as week start to week end
"Date Range",
FORMAT([Date] - WEEKDAY([Date], 2) + 1, "MMM dd") & " - " & FORMAT([Date] - WEEKDAY([Date], 2) + 7, "MMM dd"),
"MonthNumber", MONTH([Date]),
"Month", FORMAT([Date], "mmm"),
"Quarter", "QTR " & FORMAT([Date], "Q"),
"QuarterNumber", FORMAT([Date], "Q"),
"MonthYearNumber", FORMAT([Date], "yy mm"),
"Month Year", FORMAT([Date], "mmm yyyy")

It splits when the week is between two months.

How do i create the relation for the drill down to work?

[MattiaFratello]

You can calculate a new column called Month Adjusted that assigns all dates in a given week to the month in which the week begins:

Month_Adjusted =

VAR StartOfWeek =

    CALCULATE(

        MIN('Calendar'[Date]),

        FILTER(

            'Calendar',

            'Calendar'[WeekNumber] = EARLIER('Calendar'[WeekNumber]) &&

            'Calendar'[YEAR] = EARLIER('Calendar'[YEAR])

        )

    )

RETURN

    FORMAT(StartOfWeek, "MMMM")

[jeanL]

Is it possible to drill back up to the month level, where it will not calculate the result correctly. The target outcome is the bottom, where it considers 1Feb - 3Feb into February month.

Top - USING MonthAdjusted, Bottom - Using Calendar Month

[MattiaFratello]

I didn't get it, Jan 29 - Feb 04 should be part of Jan only?

[jeanL]

Yes. ideally as to follow the startofweek . So that the weeks doesnt duplicate. This below is using StartofWeek hierarchy, which works when it is drilled down to weeks, but when i drill up to Month, I need it by Calendar Month and not StartofWeek Month.