How to handle 53th week 445 calendar

[jaryszek]

Hi Guys,

I am try to use this code:
https://gorilla.bi/power-query/445-calendar/ 

let
    // ===== USER PARAMETERS =====
    StartDate = #date(2024, 1, 29),         // Enter your fiscal calendar start date
    EndDate   = #date(2030, 1, 25),         // Enter your fiscal calendar end date (or use Date.EndOfYear(DateTime.LocalNow()))
    Order445  = {4, 4, 5},                  // Pattern for months in quarter: 4-4-5; change to {4,5,4} or {5,4,4} if desired

    // ===== CREATE BASE DATE LIST =====
    Dates = List.Dates(StartDate, Duration.Days(EndDate-StartDate)+1, #duration(1,0,0,0)),
    Source = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    AddDayIndex = Table.AddIndexColumn(Source, "DayIndex", 0, 1, Int64.Type),

    // ===== FISCAL YEAR, QUARTER, PERIOD, WEEK CALCULATION =====
    DaysInYear    = List.Sum(Order445) * 4 * 7, // 364 days
    DaysInQuarter = List.Sum(Order445) * 7,     // 91 days

    // Add YearIndex (1-based)
    AddYearIndex = Table.AddColumn(AddDayIndex, "YearIndex", each Number.IntegerDivide([DayIndex], DaysInYear) + 1, Int64.Type),
    // Add Day of Fiscal Year (1-364)
    AddDayOfYear = Table.AddColumn(AddYearIndex, "DayOfYear", each Number.Mod([DayIndex], DaysInYear) + 1, Int64.Type),
    // Fiscal Year Label (as number)
    AddFiscalYear = Table.AddColumn(AddDayOfYear, "FiscalYear", each Date.Year(StartDate) + [YearIndex] - 1, Int64.Type),

    // Add QuarterIndex (1-based)
    AddQuarterIndex = Table.AddColumn(AddFiscalYear, "QuarterIndex", each Number.IntegerDivide([DayOfYear]-1, DaysInQuarter) + 1, Int64.Type),
    // Day of Quarter (1-91)
    AddDayOfQuarter = Table.AddColumn(AddQuarterIndex, "DayOfQuarter", each Number.Mod([DayOfYear]-1, DaysInQuarter) + 1, Int64.Type),

    // === Period Calculation (for 4-4-5 etc.) ===
    // Calculate WeekIndex (1-based in fiscal year)
    AddWeekIndex = Table.AddColumn(AddDayOfQuarter, "WeekIndex", each Number.IntegerDivide([DayOfYear]-1, 7) + 1, Int64.Type),

    // 13-week cycles per quarter
    AddQuarterWeek = Table.AddColumn(AddWeekIndex, "WeekOfQuarter", each Number.Mod([WeekIndex]-1, 13) + 1, Int64.Type),

    // Get the period number within quarter
    AddPeriodOfQuarter = Table.AddColumn(AddQuarterWeek, "PeriodOfQuarter",
        (row) =>
            let
                week = row[WeekOfQuarter],
                order = Order445,
                w1 = order{0},
                w2 = order{1},
                // Decide if week is in period 1, 2 or 3
                period = if week <= w1 then 1 else if week <= w1+w2 then 2 else 3
            in period,
        Int64.Type
    ),

    // Get period number in fiscal year (1..12)
    AddPeriodOfYear = Table.AddColumn(AddPeriodOfQuarter, "Period", 
        (row) => (row[QuarterIndex]-1)*3 + row[PeriodOfQuarter], Int64.Type),

    // Week of Period (1-based)
    AddWeekOfPeriod = Table.AddColumn(AddPeriodOfYear, "WeekOfPeriod",
        (row) =>
            let
                period = row[PeriodOfQuarter],
                weekq  = row[WeekOfQuarter],
                order = Order445,
                offset = if period=1 then 0 else if period=2 then order{0} else order{0}+order{1}
            in weekq - offset,
        Int64.Type
    ),

    // Day of Period
    AddDayOfPeriod = Table.AddColumn(AddWeekOfPeriod, "DayOfPeriod",
        (row) =>
            let
                period = row[PeriodOfQuarter],
                dayq = row[DayOfQuarter],
                order = Order445,
                offset = if period=1 then 0 else if period=2 then order{0}*7 else (order{0}+order{1})*7
            in dayq - offset,
        Int64.Type
    ),

    // Days in Period (28 or 35)
    AddDaysInPeriod = Table.AddColumn(AddDayOfPeriod, "DaysInPeriod",
        (row) =>
            let
                period = row[PeriodOfQuarter],
                order = Order445,
                result = order{period-1} * 7
            in result,
        Int64.Type
    ),

    // Add labels (optional)
    AddQuarterLabel = Table.AddColumn(AddDaysInPeriod, "QuarterLabel", each "Q" & Text.From([QuarterIndex])),
    AddPeriodLabel = Table.AddColumn(AddQuarterLabel, "PeriodLabel", each "P" & Text.PadStart(Text.From([Period]),2,"0")),
    AddWeekLabel = Table.AddColumn(AddPeriodLabel, "WeekLabel", each "W" & Text.PadStart(Text.From([WeekIndex]),2,"0")),

    // Week Start and End
    AddWeekStart = Table.AddColumn(AddWeekLabel, "WeekStart", each Date.AddDays([Date], -Number.Mod(Date.DayOfWeek([Date], Day.Monday), 7))),
    AddWeekEnd = Table.AddColumn(AddWeekStart, "WeekEnd", each Date.AddDays([WeekStart], 6)),

    // Fiscal Year Start and End
    AddYearStart = Table.AddColumn(AddWeekEnd, "YearStart", each Date.AddDays([Date], -([DayOfYear]-1))),
    AddYearEnd = Table.AddColumn(AddYearStart, "YearEnd", each Date.AddDays([YearStart], DaysInYear-1)),

    // Fiscal Quarter Start and End
    AddQuarterStart = Table.AddColumn(AddYearEnd, "QuarterStart", each Date.AddDays([Date], -([DayOfQuarter]-1))),
    AddQuarterEnd = Table.AddColumn(AddQuarterStart, "QuarterEnd", each Date.AddDays([QuarterStart], DaysInQuarter-1)),

    // Period Start and End
    AddPeriodStart = Table.AddColumn(AddQuarterEnd, "PeriodStart", 
        (row) => Date.AddDays(row[Date], -(row[DayOfPeriod]-1))),
    AddPeriodEnd = Table.AddColumn(AddPeriodStart, "PeriodEnd",
        (row) => Date.AddDays(row[PeriodStart], row[DaysInPeriod]-1)),

    // Reorder columns
    FinalTable = Table.SelectColumns(
        AddPeriodEnd, 
        {
            "Date", "FiscalYear", "YearStart", "YearEnd",
            "QuarterIndex", "QuarterLabel", "QuarterStart", "QuarterEnd",
            "Period", "PeriodLabel", "PeriodStart", "PeriodEnd", "DaysInPeriod",
            "WeekIndex", "WeekLabel", "WeekOfQuarter", "WeekOfPeriod", "WeekStart", "WeekEnd",
            "DayOfYear", "DayOfQuarter", "DayOfPeriod"
        }
    )
in
    FinalTable

or this code (from this video: https://www.youtube.com/watch?v=JqVnqMLGWDY )

let
     Source = List.Dates,
     // Invoke List.Dates function. Here you need to specify the dates you want the list to invoke.
    // From Date: # Date (YYYY,MM,DD)
    // To Date:  Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-  #date(YYYY, MM, DD))+1  (It counts the number of days between today and the first date of the table and adds one day more to include today).
    // To adapt this to your own calender table change the values for (YYYY; MM; DD)
    // #duration (1,0,0,0) instructs List.Date to do one day increments in the list.
    #"Invoked Dates" = Source(#date(#"StartYear",#"StartMonth",#"StartDay"), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(#"StartYear",#"StartMonth",#"StartDay")), #duration(1, 0, 0, 0)),
     // This step will convert the list to a table.
    #"List to Table" = Table.FromList(#"Invoked Dates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Rename column to Date
    Date = Table.RenameColumns(#"List to Table",{{"Column1", "Date"}}),
    // Create a Day Index, used to calculate other columns. The column index should start by 1, so click on the gear and change zero to one.
    #"Added Index" = Table.AddIndexColumn(Date, "Index", 1, 1),
    // We divide the Index column created (1, 2, 3, 4……363, 364, 365, 366 etc) by 364 and we round it up.  So 1/364  results in a very small number which when rounded up equals 1. The same happens all the way up to and including 364/364 also rounds up to 1.  But 365/364 rounds up to 2 – hence it becomes the “second year”. 
    #"YearID Added" = Table.AddColumn(#"Added Index", "YearID", each Number.RoundDown(([Index]-1)/364)+1),
    // Convert the start date to a year with Date.Year and then added the YearID.  This assumes that the finacial year is 2014. If the financial year is 2013 then remove the sum of Year ID.
    #"Year Added" = Table.AddColumn(#"YearID Added", "Fin Year", each Date.Year(#date(#"StartYear",#"StartMonth",#"StartDay")) + [YearID]),
    // Quater ID added by dividing the index into periods of 90 days
    #"QuarterID Added" = Table.AddColumn(#"Year Added", "QuarterID", each Number.RoundDown(([Index]-1)/91)+1),
    // This column re-starts the Week count when a new year begins.
    // Source:http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/
    // 
    #"QuarterNo Added" = Table.AddColumn(#"QuarterID Added", "Fin QuarterNo", each [QuarterID]-Number.RoundUp(([QuarterID]/90)-1)*90),
    // The formula for this column depends on which version of the calendar you are using.  By choosing a calendar type parameter the appropriate formula will be selected.
    // Source:http://www.excelguru.ca/blog/2016/01/06/creating-a-custom-calendar-in-power-query/
    #"MonthID Added" = Table.AddColumn(#"QuarterNo Added", "MonthID", each if CalendarType="445" then 
Number.RoundDown([Index]/91)*3+
( if Number.Mod([Index],91)=0 then 0
else if Number.Mod([Index],91)<=28 then 1
else if Number.Mod([Index],91)<=56 then 2
else 3
)
 else
if CalendarType="454" then Number.RoundDown([Index]/91)*3+
( if Number.Mod([Index],91)=0 then 0
else if Number.Mod([Index],91)<=28 then 1
else if Number.Mod([Index],91)<=63 then 2
else 3
)
 else 
if CalendarType="544" then Number.RoundDown([Index]/91)*3+
( if Number.Mod([Index],91)=0 then 0
else if Number.Mod([Index],91)<=35 then 1
else if Number.Mod([Index],91)<=63 then 2
else 3
)
 else
if CalendarType="13 weeks" then Number.RoundDown(([Index]-1)/28)+1 

else "Wrong calendar type"),
    // This column re-starts the Week count when a new year begins.
    // Source:http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/
    #"MonthNo Added" = Table.AddColumn(#"MonthID Added", "Fin MonthNo", each [MonthID]  - (Number.RoundUp([MonthID]/12)-1)*12),
    // Navigate here for other Languages: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx
    #"MonthName Added" = Table.AddColumn(#"MonthNo Added", "Fin MonthName", each if [Fin MonthNo] <=8 then Date.ToText(#date([Fin Year],[Fin MonthNo]+4,1),"MMM",#"Language")

else 

Date.ToText(#date([Fin Year],[Fin MonthNo]-8,1),"MMM",#"Language")),
    // Create a column that increases its value every 7 days. It does that thoughout the entire calendar.
    #"WeekID Added" = Table.AddColumn(#"MonthName Added", "WeekID", each Number.RoundUp([Index]/7)),
    // This column re-starts the Week count when a new year begins.
    // Source:http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/
    #"Week Added" = Table.AddColumn(#"WeekID Added", "Fin WeekNo", each [WeekID] - (Number.RoundUp([WeekID]/52)-1)*52),
    // Extract Day from Date column and add leading zeros “00”
    #"DayNo Added" = Table.AddColumn(#"Week Added", "DayNo", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
    // Navigate here for other Languages: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx
    #"DayName Added" = Table.AddColumn(#"DayNo Added", "DayName", each Date.ToText([Date],"ddd",#"Language")),
    // Concatenate Year and month
    #"YearMonth Added" = Table.AddColumn(#"DayName Added", "YearMonth", each Number.ToText([Fin Year])&"-"&[Fin MonthName]),
    // Concatenate Year and Quarter No with leading zeros on quarter Nos.
    #"Year Quarter" = Table.AddColumn(#"YearMonth Added", "YearQuarter", each Number.ToText([Fin Year]) & "Q"& Number.ToText([Fin QuarterNo],"00")),
    #"Sort DayName" = Table.AddColumn(#"Year Quarter", "SortDayName", each Date.DayOfWeek([Date],#"StartWeekDay")),
    // Convert yearmonth to number to sort in powerpivot.
    #"Sort YearMonth" = Table.AddColumn(#"Sort DayName", "Sort YearMonth", each Number.ToText([Fin Year])&Text.PadStart(Number.ToText([Fin MonthNo]),2,"0")),
    // Convert yearquarter to number to sort in powerpivot.
    #"Sort YearQuarter" = Table.AddColumn(#"Sort YearMonth", "Sort YearQuarter", each Number.ToText([Fin Year])&Text.PadStart(Number.ToText([Fin QuarterNo]),2,"0")),
    #"Format Columns" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"Fin MonthNo", Int64.Type}, {"WeekID", Int64.Type}, {"MonthID", Int64.Type}, {"YearID", Int64.Type}, {"Index", Int64.Type}, {"Fin Year", Int64.Type}, {"Fin WeekNo", Int64.Type}, {"Fin MonthName", type text}, {"DayName", type text}, {"YearMonth", type text}, {"DayNo", Int64.Type}, {"SortDayName", Int64.Type}, {"Fin QuarterNo", Int64.Type}, {"QuarterID", Int64.Type}, {"Sort YearMonth", Int64.Type}, {"Sort YearQuarter", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Format Columns",{"Index", "YearID", "MonthID", "WeekID", "QuarterID"})
in
    #"Removed Columns"

in order to create 445 calendar.
But there is no handler for 53th week every 5 or 6 years. 

How you are handling this? 
Best wishes,
Jacek

[lbendlin]

Calendars are immutable. There is no point trying to do this in Power Query or DAX.  Use an external precomputed reference table.

[jaryszek]

thank you, i will do this first in DAX, after i will move outside. 

[Ahmedx]

pls try

let
    // ===== USER PARAMETERS =====
    StartDate = #date(2024, 1, 29),         // Enter your fiscal calendar start date
    EndDate   = #date(2030, 1, 25),         // Enter your fiscal calendar end date
    Order445  = {4, 4, 5},                  // Pattern for months in quarter: 4-4-5

    // ===== 53RD WEEK LOGIC =====
    // Function to determine if a fiscal year needs 53 weeks
    Needs53Weeks = (fiscalYearStart as date) as logical =>
        let
            // Method 1: Check if fiscal year start falls on Thursday, Friday, or Saturday
            startDayOfWeek = Date.DayOfWeek(fiscalYearStart, Day.Monday), // 0=Monday, 6=Sunday
            needsExtra = startDayOfWeek >= 3 and startDayOfWeek <= 5 // Thu, Fri, Sat
        in needsExtra,

    // Alternative method: Check if adding 364 days overshoots the next fiscal year start
    // This is more accurate for custom fiscal calendars
    Needs53WeeksAlt = (fiscalYearStart as date, nextFiscalYearStart as date) as logical =>
        let
            standardYearEnd = Date.AddDays(fiscalYearStart, 363), // 364 days - 1
            daysBetween = Duration.Days(nextFiscalYearStart - fiscalYearStart),
            needsExtra = daysBetween > 364
        in needsExtra,

    // ===== CREATE BASE DATE LIST =====
    Dates = List.Dates(StartDate, Duration.Days(EndDate-StartDate)+1, #duration(1,0,0,0)),
    Source = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    AddDayIndex = Table.AddIndexColumn(Source, "DayIndex", 0, 1, Int64.Type),

    // ===== FISCAL YEAR CALCULATION WITH 53RD WEEK =====
    BaseDaysInYear = List.Sum(Order445) * 4 * 7, // 364 days
    DaysInQuarter = List.Sum(Order445) * 7,       // 91 days

    // Determine fiscal year boundaries considering 53rd week
    AddFiscalYearInfo = Table.AddColumn(AddDayIndex, "FiscalYearInfo", 
        (row) =>
            let
                currentDate = row[Date],
                yearsSinceStart = Number.IntegerDivide(row[DayIndex], BaseDaysInYear),
                
                // Calculate this fiscal year's start
                baseYearStart = Date.AddDays(StartDate, yearsSinceStart * BaseDaysInYear),
                
                // Check if this fiscal year needs 53 weeks
                needs53 = Needs53Weeks(baseYearStart),
                actualDaysInYear = if needs53 then BaseDaysInYear + 7 else BaseDaysInYear,
                
                // Recalculate year index with variable year lengths
                adjustedYearIndex = 
                    let
                        totalDays = 0,
                        yearIndex = 1,
                        findYear = List.Generate(
                            () => [year = 1, cumulativeDays = 0],
                            each [cumulativeDays] <= row[DayIndex],
                            each 
                                let
                                    yearStart = Date.AddDays(StartDate, [cumulativeDays]),
                                    yearNeeds53 = Needs53Weeks(yearStart),
                                    yearDays = if yearNeeds53 then BaseDaysInYear + 7 else BaseDaysInYear
                                in [year = [year] + 1, cumulativeDays = [cumulativeDays] + yearDays]
                        ),
                        lastItem = List.Last(findYear)
                    in lastItem[year] - 1,
                
                fiscalYear = Date.Year(StartDate) + adjustedYearIndex - 1
            in
                [
                    YearIndex = adjustedYearIndex,
                    FiscalYear = fiscalYear,
                    Has53Weeks = needs53,
                    DaysInThisYear = actualDaysInYear
                ],
        type record
    ),

    // Extract fiscal year info
    AddYearIndex = Table.AddColumn(AddFiscalYearInfo, "YearIndex", each [FiscalYearInfo][YearIndex], Int64.Type),
    AddFiscalYear = Table.AddColumn(AddYearIndex, "FiscalYear", each [FiscalYearInfo][FiscalYear], Int64.Type),
    AddHas53Weeks = Table.AddColumn(AddFiscalYear, "Has53Weeks", each [FiscalYearInfo][Has53Weeks], Logical.Type),
    AddDaysInYear = Table.AddColumn(AddHas53Weeks, "DaysInThisYear", each [FiscalYearInfo][DaysInThisYear], Int64.Type),

    // Calculate day of fiscal year (1-364 or 1-371)
    AddDayOfYear = Table.AddColumn(AddDaysInYear, "DayOfYear", 
        (row) =>
            let
                // Find the start of this fiscal year
                yearStartDayIndex = 
                    let
                        findStart = List.Generate(
                            () => [year = 1, cumulativeDays = 0],
                            each [year] < row[YearIndex],
                            each 
                                let
                                    yearStart = Date.AddDays(StartDate, [cumulativeDays]),
                                    yearNeeds53 = Needs53Weeks(yearStart),
                                    yearDays = if yearNeeds53 then BaseDaysInYear + 7 else BaseDaysInYear
                                in [year = [year] + 1, cumulativeDays = [cumulativeDays] + yearDays]
                        ),
                        result = if List.Count(findStart) = 0 then 0 else List.Last(findStart)[cumulativeDays]
                    in result,
                dayOfYear = row[DayIndex] - yearStartDayIndex + 1
            in dayOfYear,
        Int64.Type
    ),

    // ===== QUARTER AND PERIOD CALCULATION =====
    AddQuarterInfo = Table.AddColumn(AddDayOfYear, "QuarterInfo",
        (row) =>
            let
                dayOfYear = row[DayOfYear],
                has53Weeks = row[Has53Weeks],
                
                // Standard quarter calculation
                quarterIndex = Number.IntegerDivide(dayOfYear-1, DaysInQuarter) + 1,
                dayOfQuarter = Number.Mod(dayOfYear-1, DaysInQuarter) + 1,
                
                // Adjust for 53rd week (add to Q4)
                adjustedQuarter = if has53Weeks and dayOfYear > (DaysInQuarter * 3) then
                    [
                        QuarterIndex = 4,
                        DayOfQuarter = dayOfYear - (DaysInQuarter * 3),
                        DaysInThisQuarter = DaysInQuarter + 7  // Q4 gets extra week
                    ]
                else if quarterIndex > 4 then  // Safety check
                    [
                        QuarterIndex = 4,
                        DayOfQuarter = dayOfQuarter + DaysInQuarter,
                        DaysInThisQuarter = DaysInQuarter + 7
                    ]
                else
                    [
                        QuarterIndex = quarterIndex,
                        DayOfQuarter = dayOfQuarter,
                        DaysInThisQuarter = if quarterIndex = 4 and has53Weeks then DaysInQuarter + 7 else DaysInQuarter
                    ]
            in adjustedQuarter,
        type record
    ),

    AddQuarterIndex = Table.AddColumn(AddQuarterInfo, "QuarterIndex", each [QuarterInfo][QuarterIndex], Int64.Type),
    AddDayOfQuarter = Table.AddColumn(AddQuarterIndex, "DayOfQuarter", each [QuarterInfo][DayOfQuarter], Int64.Type),
    AddDaysInQuarter = Table.AddColumn(AddDayOfQuarter, "DaysInThisQuarter", each [QuarterInfo][DaysInThisQuarter], Int64.Type),

    // ===== PERIOD CALCULATION WITH 53RD WEEK =====
    AddPeriodInfo = Table.AddColumn(AddDaysInQuarter, "PeriodInfo",
        (row) =>
            let
                dayOfQuarter = row[DayOfQuarter],
                quarterIndex = row[QuarterIndex],
                has53Weeks = row[Has53Weeks],
                order = Order445,
                
                // Calculate period within quarter
                periodOfQuarter = 
                    if dayOfQuarter <= order{0} * 7 then 1
                    else if dayOfQuarter <= (order{0} + order{1}) * 7 then 2
                    else 3,
                
                // For 53rd week: add to last period of last quarter (Period 12)
                isLastPeriodWith53 = quarterIndex = 4 and periodOfQuarter = 3 and has53Weeks,
                
                // Calculate days in this period
                daysInPeriod = 
                    let
                        standardDays = order{periodOfQuarter-1} * 7
                    in
                        if isLastPeriodWith53 then standardDays + 7 else standardDays,
                
                // Calculate day of period
                dayOfPeriod = 
                    let
                        offset = if periodOfQuarter = 1 then 0 
                                else if periodOfQuarter = 2 then order{0} * 7 
                                else (order{0} + order{1}) * 7
                    in dayOfQuarter - offset,
                
                // Period number in fiscal year
                periodOfYear = (quarterIndex - 1) * 3 + periodOfQuarter
            in
                [
                    PeriodOfQuarter = periodOfQuarter,
                    Period = periodOfYear,
                    DayOfPeriod = dayOfPeriod,
                    DaysInPeriod = daysInPeriod,
                    IsLeapPeriod = isLastPeriodWith53
                ],
        type record
    ),

    AddPeriodOfQuarter = Table.AddColumn(AddPeriodInfo, "PeriodOfQuarter", each [PeriodInfo][PeriodOfQuarter], Int64.Type),
    AddPeriod = Table.AddColumn(AddPeriodOfQuarter, "Period", each [PeriodInfo][Period], Int64.Type),
    AddDayOfPeriod = Table.AddColumn(AddPeriod, "DayOfPeriod", each [PeriodInfo][DayOfPeriod], Int64.Type),
    AddDaysInPeriod = Table.AddColumn(AddDayOfPeriod, "DaysInPeriod", each [PeriodInfo][DaysInPeriod], Int64.Type),
    AddIsLeapPeriod = Table.AddColumn(AddDaysInPeriod, "IsLeapPeriod", each [PeriodInfo][IsLeapPeriod], Logical.Type),

    // ===== WEEK CALCULATIONS =====
    AddWeekIndex = Table.AddColumn(AddIsLeapPeriod, "WeekIndex", each Number.IntegerDivide([DayOfYear]-1, 7) + 1, Int64.Type),
    AddWeekOfQuarter = Table.AddColumn(AddWeekIndex, "WeekOfQuarter", each Number.Mod([WeekIndex]-1, if [Has53Weeks] and [QuarterIndex] = 4 then 14 else 13) + 1, Int64.Type),
    AddWeekOfPeriod = Table.AddColumn(AddWeekOfQuarter, "WeekOfPeriod", each Number.IntegerDivide([DayOfPeriod]-1, 7) + 1, Int64.Type),

    // ===== LABELS =====
    AddQuarterLabel = Table.AddColumn(AddWeekOfPeriod, "QuarterLabel", each "Q" & Text.From([QuarterIndex])),
    AddPeriodLabel = Table.AddColumn(AddQuarterLabel, "PeriodLabel", each "P" & Text.PadStart(Text.From([Period]),2,"0")),
    AddWeekLabel = Table.AddColumn(AddPeriodLabel, "WeekLabel", each "W" & Text.PadStart(Text.From([WeekIndex]),2,"0")),

    // ===== DATE RANGES =====
    // Week Start and End (assuming weeks start on Monday)
    AddWeekStart = Table.AddColumn(AddWeekLabel, "WeekStart", each Date.AddDays([Date], -Number.Mod(Date.DayOfWeek([Date], Day.Monday), 7))),
    AddWeekEnd = Table.AddColumn(AddWeekStart, "WeekEnd", each Date.AddDays([WeekStart], 6)),

    // Fiscal Year Start and End
    AddYearStart = Table.AddColumn(AddWeekEnd, "YearStart", each Date.AddDays([Date], -([DayOfYear]-1))),
    AddYearEnd = Table.AddColumn(AddYearStart, "YearEnd", each Date.AddDays([YearStart], [DaysInThisYear]-1)),

    // Fiscal Quarter Start and End
    AddQuarterStart = Table.AddColumn(AddYearEnd, "QuarterStart", each Date.AddDays([Date], -([DayOfQuarter]-1))),
    AddQuarterEnd = Table.AddColumn(AddQuarterStart, "QuarterEnd", each Date.AddDays([QuarterStart], [DaysInThisQuarter]-1)),

    // Period Start and End
    AddPeriodStart = Table.AddColumn(AddQuarterEnd, "PeriodStart", each Date.AddDays([Date], -([DayOfPeriod]-1))),
    AddPeriodEnd = Table.AddColumn(AddPeriodStart, "PeriodEnd", each Date.AddDays([PeriodStart], [DaysInPeriod]-1)),

    // Clean up and reorder columns
    RemoveHelperColumns = Table.RemoveColumns(AddPeriodEnd, {"DayIndex", "FiscalYearInfo", "QuarterInfo", "PeriodInfo"}),
    
    FinalTable = Table.SelectColumns(
        RemoveHelperColumns, 
        {
            "Date", "FiscalYear", "YearStart", "YearEnd", "Has53Weeks", "DaysInThisYear",
            "QuarterIndex", "QuarterLabel", "QuarterStart", "QuarterEnd", "DaysInThisQuarter",
            "Period", "PeriodLabel", "PeriodStart", "PeriodEnd", "DaysInPeriod", "IsLeapPeriod",
            "WeekIndex", "WeekLabel", "WeekOfQuarter", "WeekOfPeriod", "WeekStart", "WeekEnd",
            "DayOfYear", "DayOfQuarter", "DayOfPeriod"
        }
    )
in
    FinalTable