SQLBI 4-4-5 Calendar - Need 53rd week to be in 2021, not 2020

[OCBB_SFAFPandA]

Hi,

I am using this table from SQLBI and it's very helpful, only issue is how it treats the 53rd week. Think we only need to look at the 1st couple paragraphs for the setup, but how would I need to adjust this to have the 53rd week happen in 2021, not 2020?

Date = 
VAR FirstFiscalMonth = 1       -- First month of the fiscal year
VAR FirstDayOfWeek = 0         -- 0 = Sunday, 1 = Monday, ...
VAR FirstSalesDate = MIN ( DIM_PMA_Invoice[Invoice Date] )
VAR LastSalesDate = MAX ( DIM_PMA_Invoice[Invoice Date] )
VAR TypeStartFiscalYear = 0     -- Fiscal year as Calendar Year of : 
                                -- 0 - First day of fiscal year
                                -- 1 - Last day of fiscal year
VAR QuarterWeekType = "445" -- Supports only "445", "454", and "544"
VAR WeeklyType = "Nearest" -- Use: "Nearest" or "Last" 

-- Last:    for last weekday of the month at fiscal year end
-- Nearest: for last weekday nearest the end of month 
-- Reference for Last/Nearest definition: https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar)
--
-- For ISO calendar use 
--   FirstFiscalMonth = 1 (ISO always starts in January)
--   FirstDayOfWeek = 1           (ISO always starts on Monday)
--   WeeklyType = "Nearest"       (ISO use the nearest week type algorithm)
-- For US with last Saturday of the month at fiscal year end
--   FirstDayOfWeek = 0           (US weeks start on Sunday)
--   WeeklyType = "Last"
-- For US with last Saturday nearest the end of month
--   FirstDayOfWeek = 0           (US weeks start on Sunday)
--   WeeklyType = "Nearest"
--
------------------------------
VAR FirstYear =
    YEAR ( FirstSalesDate )
        + 1 * ( MONTH ( FirstSalesDate ) >= FirstFiscalMonth
                    && FirstFiscalMonth > 1 )
VAR LastYear =
    YEAR ( LastSalesDate )
        + 1 * ( MONTH ( LastSalesDate ) >= FirstFiscalMonth
                    && FirstFiscalMonth > 1 )

VAR WorkingDayType = "Working Day" -- Description for working days
VAR NonWorkingDayType = "Non-Working day" -- Description for non-working days
------------------------------
-- Set the working days - 0 = Sunday, 1 = Monday, ... 6 = Saturday
VAR WorkingDays =
    DATATABLE ( "WorkingDayNumber", INTEGER, { { 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 } } ) --
------------------------------------------------------------
--  
-- End of General Configuration
--
------------------------------------------------------------
VAR FirstDayCalendar =
    DATE ( FirstYear - 1, 1, 1 )
VAR LastDayCalendar =
    //DATE ( LastYear + 1, 12, 31 ) - changed this to have last date the invoice date
        LastSalesDate
VAR WeekDayCalculationType =
    IF ( FirstDayOfWeek = 0, 7, FirstDayOfWeek ) + 10
VAR OffsetFiscalYear =
    IF ( FirstFiscalMonth > 1, 1, 0 )
VAR WeeklyFiscalPeriods =
    GENERATE (
        SELECTCOLUMNS (
            GENERATESERIES ( FirstYear - OffsetFiscalYear, LastYear + OffsetFiscalYear, 1 ),
            "FW YearNumber", [Value]
        ),
        VAR StartFiscalYearNumber = [FW YearNumber] - ( OffsetFiscalYear * TypeStartFiscalYear )
        VAR FirstDayCurrentYear =
            DATE ( StartFiscalYearNumber, FirstFiscalMonth, 1 )
        VAR FirstDayNextYear =
            DATE ( StartFiscalYearNumber + 1, FirstFiscalMonth, 1 )
        VAR DayOfWeekNumberCurrentYear =
            WEEKDAY ( FirstDayCurrentYear, WeekDayCalculationType )
        VAR OffsetStartCurrentFiscalYear =
            SWITCH (
                WeeklyType,
                "Last", 1 - DayOfWeekNumberCurrentYear,
                "Nearest", IF (
                    DayOfWeekNumberCurrentYear >= 5,
                    8 - DayOfWeekNumberCurrentYear,
                    1 - DayOfWeekNumberCurrentYear
                ),
                ERROR ( "Unkonwn WeeklyType definition" )
            )
        VAR DayOfWeekNumberNextYear =
            WEEKDAY ( FirstDayNextYear, WeekDayCalculationType )
        VAR OffsetStartNextFiscalYear =
            SWITCH (
                WeeklyType,
                "Last", - DayOfWeekNumberNextYear,
                "Nearest", IF (
                    DayOfWeekNumberNextYear >= 5,
                    7 - DayOfWeekNumberNextYear,
                    - DayOfWeekNumberNextYear
                ),
                ERROR ( "Unkonwn WeeklyType definition : " )
            )
        VAR FirstDayOfFiscalYear = FirstDayCurrentYear + OffsetStartCurrentFiscalYear
        VAR LastDayOfFiscalYear = FirstDayNextYear + OffsetStartNextFiscalYear
        RETURN
            ROW (
                "FW StartOfYear", FirstDayOfFiscalYear,
                "FW EndOfYear", LastDayOfFiscalYear
            )
    )
VAR WeeksInP1 =
    SWITCH (
        QuarterWeekType,
        "445", 4,
        "454", 4,
        "544", 5,
        ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
    )
VAR WeeksInP2 =
    SWITCH (
        QuarterWeekType,
        "445", 4,
        "454", 5,
        "544", 4,
        ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
    )
VAR WeeksInP3 =
    SWITCH (
        QuarterWeekType,
        "445", 5,
        "454", 4,
        "544", 4,
        ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
    )
VAR FirstSundayReference =
    DATE ( 1900, 12, 30 ) -- Do not change this 
VAR FirstWeekReference = FirstSundayReference + FirstDayOfWeek
VAR CustomFiscalRawDays =
    GENERATE ( WeeklyFiscalPeriods, CALENDAR ( [FW StartOfYear], [FW EndOfYear] ) )
VAR FiscalWeeksBase =
    GENERATE (
        CustomFiscalRawDays,
        VAR CalDate = [Date]
        VAR FwFirstDayOfYear = [FW StartOfYear]
        VAR FwDayOfYear =
            INT ( CalDate - FwFirstDayOfYear + 1 )
        VAR CalYear =
            YEAR ( [Date] )
        VAR CalMonthNumber =
            MONTH ( [Date] )
        VAR CalDay =
            DAY ( [Date] )
        VAR FwDayOfYearNumber = CalDate - [FW StartOfYear] + 1
        VAR FwWeekNumber =
            INT ( CEILING ( FwDayOfYearNumber / 7, 1 ) )
        VAR FwPeriodNumber =
            IF ( FwWeekNumber > 52, 14, ROUNDUP ( FwWeekNumber / 4, 0 ) )
        VAR FwYearNumber = [FW YearNumber]
        VAR FwQuarterNumber =
            IF ( FwWeekNumber > 52, 4, ROUNDUP ( FwWeekNumber / 13, 0 ) )
        VAR FwWeekInQuarterNumber =
            IF ( FwWeekNumber > 52, 14, FwWeekNumber - 13 * ( FwQuarterNumber - 1 ) )
        VAR FwMonthNumber =
            ( FwQuarterNumber - 1 ) * 3
                + SWITCH (
                    TRUE,
                    FwWeekInQuarterNumber <= WeeksInP1, 1,
                    FwWeekInQuarterNumber <= ( WeeksInP1 + WeeksInP2 ), 2,
                    3
                )
        VAR WeekDayNumber =
            WEEKDAY ( CalDate, WeekDayCalculationType )
        VAR FirstDayOfWeek = [Date] - WeekDayNumber + 1
        VAR LastDayOfWeek = FirstDayOfWeek + 6
        VAR IsWorkingDay =
            CONTAINS ( WorkingDays, [WorkingDayNumber], WEEKDAY ( CalDate, 1 ) - 1 )
        RETURN
            ROW (
                "DateKey", CalYear * 10000 + CalMonthNumber * 100 + CalDay,
                "FW Year", FwYearNumber,
                "FW QuarterNumber", FwQuarterNumber,
                "FW Quarter", "FQ" & FwQuarterNumber,
                "FW YearQuarterNumber", FwYearNumber * 4 - 1 + FwQuarterNumber,
                "FW Quarter Year", "FQ" & FwQuarterNumber & "-" & FwYearNumber,
                "FW MonthNumber", FwMonthNumber,
                "FW YearMonthNumber", FwYearNumber * 12 - 1 + FwMonthNumber,
                "FW WeekNumber", FwWeekNumber,
                "FW Week", "FW" & FORMAT ( FwWeekNumber, "00" ),
                "FW PeriodNumber", FwPeriodNumber,
                "FW Period", "P" & FORMAT ( FwPeriodNumber, "00" ),
                "FW YearWeekNumber", INT ( DIVIDE ( CalDate - FirstWeekReference, 7 ) ) + 1,
                "FW Week Year", "FW" & FORMAT ( FwWeekNumber, "00" ) & "-" & FwYearNumber,
                "FW StartOfWeek", FirstDayOfWeek,
                "FW EndOfWeek", LastDayOfWeek,
                "WeekDayNumber", WeekDayNumber,
                "Week Day", FORMAT ( CalDate, "ddd" ),
                "FW DayOfYearNumber", FwDayOfYear,
                "IsWorkingDay", IsWorkingDay,
                "Day Type", IF ( IsWorkingDay, WorkingDayType, NonWorkingDayType )
            )
    )
VAR FiscalWeeks_Pre =
    GENERATE (
        FiscalWeeksBase,
        VAR CalDate = [Date]
        VAR FWYearNumber = [FW YearNumber]
        VAR FwYearWeekNumber = [FW YearWeekNumber]
        VAR FwYearMonthNumber = [FW YearMonthNumber]
        VAR FwYearQuarterNumber = [FW YearQuarterNumber]
        VAR FwStartOfMonth =
            MINX (
                FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
                [Date]
            )
        VAR FwEndOfMonth =
            MAXX (
                FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
                [Date]
            )
        VAR FwMonthDays =
            INT ( FwEndOfMonth - FwStartOfMonth + 1 )
        VAR FwDayOfMonthNumber =
            INT ( CalDate - FwStartOfMonth + 1 )
        VAR FwStartOfQuarter =
            MINX (
                FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
                [Date]
            )
        VAR FwEndOfQuarter =
            MAXX (
                FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
                [Date]
            )
        VAR FwQuarterDays =
            INT ( FwEndOfQuarter - FwStartOfQuarter + 1 )
        VAR FwDayOfQuarterNumber =
            INT ( CalDate - FwStartOfQuarter + 1 )
        VAR FwStartOfYear =
            MINX ( FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ), [Date] )
        VAR FwEndOfYear =
            MAXX ( FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ), [Date] )
        VAR FwYearDays =
            INT ( FwEndOfYear - FwStartOfYear + 1 )
        RETURN
            ROW (
                "FW Month", "FM " & FORMAT ( FwStartOfMonth + 14, "mmm" ),
                "FW Month Year", "FM " & FORMAT ( FwStartOfMonth + 14, "mmm yyyy" ),
                "FW StartOfMonth", FwStartOfMonth,
                "FW EndOfMonth", FwEndOfMonth,
                "FW DayOfMonthNumber", FwDayOfMonthNumber,
                "FW StartOfQuarter", FwStartOfQuarter,
                "FW EndOfQuarter", FwEndOfQuarter,
                "FW DayOfQuarterNumber", FwDayOfQuarterNumber,
                "FW MonthDays", FwMonthDays,
                "FW QuarterDays", FwQuarterDays,
                "FW YearDays", FwYearDays
            )
    )
VAR FiscalWeeks =
    GENERATE (
        FiscalWeeks_Pre,
        VAR CalDate = [Date]
        VAR FwYearMonthNumber = [FW YearMonthNumber]
        VAR FwYearQuarterNumber = [FW YearQuarterNumber]
        VAR FWYearNumber = [FW YearNumber]
        VAR FwDayOfMonthNumber = [FW DayOfMonthNumber]
        VAR FwDayOfQuarterNumber = [FW DayOfQuarterNumber]
        VAR FwDayOfYearNumber = [FW DayOfYearNumber]
        VAR FwMonthDays = [FW EndOfMonth] - [FW StartOfMonth] + 1
        VAR FwQuarterDays = [FW EndOfQuarter] - [FW StartOfQuarter] + 1
        VAR FwYearDays = [FW EndOfYear] - [FW StartOfYear] + 1
        VAR FwDatePreviousWeek = CalDate - 7
        VAR FwDatePreviousMonth =
            MAXX (
                FILTER (
                    FiscalWeeks_Pre,
                    [Fw YearMonthNumber] = FwYearMonthNumber - 1
                        && ( [FW DayOfMonthNumber] <= FwDayOfMonthNumber
                        || FwDayOfMonthNumber = FwMonthDays )
                ),
                [Date]
            )
        VAR FwDatePreviousQuarter =
            MAXX (
                FILTER (
                    FiscalWeeks_Pre,
                    [Fw YearQuarterNumber] = FwYearQuarterNumber - 1
                        && ( [FW DayOfQuarterNumber] <= FwDayOfQuarterNumber
                        || FwDayOfQuarterNumber = FwQuarterDays )
                ),
                [Date]
            )
        VAR FwDatePreviousYear =
            MAXX (
                FILTER (
                    FiscalWeeks_Pre,
                    [Fw YearNumber] = FWYearNumber - 1
                        && ( [FW DayOfYearNumber] <= FwDayOfYearNumber
                        || FwDayOfYearNumber = FwYearDays )
                ),
                [Date]
            )
        RETURN
            ROW (
                "FW DatePreviousWeek", FwDatePreviousWeek,
                "FW DatePreviousMonth", FwDatePreviousMonth,
                "FW DatePreviousQuarter", FwDatePreviousQuarter,
                "FW DatePreviousYear", FwDatePreviousYear
            )
    )
VAR CompleteCalendar =
    FILTER (
        FiscalWeeks,
        ( [FW YearNumber] >= FirstYear
            && [FW YearNumber] <= LastYear )
    )
VAR Result =
    SELECTCOLUMNS (
        CompleteCalendar,
        -- Base date columns
        "Date", [Date],
        "Sequential Day Number", INT ( [Date] ),
        -- FW = Fiscal Weekly calendar
        "Fiscal Year Number", [FW YearNumber],
        "Fiscal Year", "FY " & [FW Year],
        "Fiscal Quarter Number", [FW QuarterNumber],
        "Fiscal Quarter", [FW Quarter],
        "Fiscal Year Quarter Number", [FW YearQuarterNumber],
        "Fiscal Year Quarter", [FW Quarter Year],
        "Fiscal Month Number", [FW MonthNumber],
        "Fiscal Month", [FW Month],
        "Fiscal Year Month Number", [FW YearMonthNumber],
        "Fiscal Year Month", [FW Month Year],
        "Fiscal Week Number", [FW WeekNumber],
        "Fiscal Week", [FW Week],
        "Fiscal Year Week Number", [FW YearWeekNumber],
        "Fiscal Year Week", [FW Week Year],

        -- Holidays and working days
        "Working Day", [Day Type],

        "Day of Week Number", [WeekDayNumber],
        "Day of Week_", [Week Day],

        "Day of Fiscal Month Number", [FW DayOfMonthNumber],
        "Day of Fiscal Quarter Number", [FW DayOfQuarterNumber],
        "Day of Fiscal Year Number", [FW DayOfYearNumber]
    )
RETURN
    Result

 Here is the data validation I did

[amitchandak]

@OCBB_SFAFPandA , Refer to week, I as using Monday week , you can use week(_stDt,1)

Cal 445 = var _cal = CALENDAR(Date(2018,01,01), Date(2022,12,31))
return ADDCOLUMNS( _cal
,"Year" , QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Year" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Qtr" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4) +1
,"Week of Month" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13) 
        var _2  = if(_1=12, 5, mod(_1,4) +1) 
           return _2
,"Month no" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13) 
        var _2  = if(_1=12, 3, QUOTIENT( _1,4) +1) 
           return Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 +  _2
,"Year Month" , (QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1)*100 + Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Week" , 
Var _st = Minx(_cal, [Date])
Var _stDt =  _st -WEEKDAY(_St,2) +1 return 
Mod(QUOTIENT( DATEDIFF(_stDt, [Date], day) ,7),52)+1
)

https://amitchandak.medium.com/cheat-sheet-power-bi-4-4-4-and-4-4-5-calendar-786f76da7d92