Strange behavior with custom weekly calendar

Hi SalvaC ,

In order to solve your pain points, you can create a calendar table like below:

FiscalWeekCalendar = 
VAR MinDate = MIN( Sales[Date] ) - 370 // Ensure we go back at least one full year
VAR MaxDate = MAX( Sales[Date] )
RETURN
ADDCOLUMNS (
    CALENDAR ( MinDate, MaxDate ),
    
    // 1. Define the Fiscal Year (e.g., "22/23")
    "FiscalYearForWeek",
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR y2 = y1 + 1
        RETURN FORMAT ( y1, "00" ) & "/" & FORMAT ( y2, "00" ),

    // 2. Define the Fiscal Week Number (e.g., 1, 2, 3... 53)
    // This MUST reset for each new fiscal year
    "FiscalWeekNum", 
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR FYStartDate = DATE( y1, 8, 1 )
        // Find the start of the first week (e.g., Monday) of that fiscal year
        VAR FirstDayOfFirstWeek = FYStartDate - WEEKDAY( FYStartDate, 2 ) + 1
        // Calculate the week number
        VAR WeekNum = INT( ( [Date] - FirstDayOfFirstWeek ) / 7 ) + 1
        RETURN WeekNum,

    // 3. Define a Sort Column for "Week of Year" (e.g., 202201, 202202...)
    // This gives a unique, sortable value for every week in history
    "FiscalWeekSort", 
        VAR y1 = YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 8, 0, -1 )
        VAR FYStartDate = DATE( y1, 8, 1 )
        VAR FirstDayOfFirstWeek = FYStartDate - WEEKDAY( FYStartDate, 2 ) + 1
        VAR WeekNum = INT( ( [Date] - FirstDayOfFirstWeek ) / 7 ) + 1
        // Create a key like YYYYWW
        RETURN (y1 * 100) + WeekNum
)

Then create the matrix as in your example and the resultant output is as shown below:

I have attached an example pbix file for your reference.

Best regards,