13 Period Fiscal Calendar (Power Query M function)

Hi everyone,

Wanted to share this M code function for a 13 Period Date table with you (@DarrenG this code includes some additional extensions, so I hope you like them :wink: ).

13 periods means this calendar has 13 “months” per year, each made up of 4 weeks. Unlike a standard Date table, this custom calendar will always have 364 days per year (52 weeks x 7 days), so that the year end is different in every year.

To Invoke this function you’ll need to enter your fiscal calendar start date as FirstFYDate so that must be the first date of (one of) your fiscal year(s).

Optionally you can also set:
StartOfWeekDay as number where 0 = Sunday, 1 = Monday and so on
AddYears as number of years to add however if you don’t this will be dynamic.
Holidays as list
.

let fnDateTable = ( FirstFYDate as date, optional StartOfWeekDay as number, optional AddYears as number, optional Holidays as list ) as table =>
    let
        FYStartDate = FirstFYDate, // #date(2019, 9, 29), // Enter your first FY StartDate here
        StartOfWeekNum = if List.Contains({0..6}, StartOfWeekDay) then StartOfWeekDay else 0, // Day.Sunday =0, Day.Monday =1 etc
        CurrentDate = Date.From( DateTimeZone.FixedUtcNow()),
        ExtendYears = if AddYears = null or AddYears =0 then Date.Year( CurrentDate ) -  Date.Year( FYStartDate ) +1 else AddYears,
        StartDate = Number.From( FYStartDate ),
        EndDate = StartDate + ( 364 * ExtendYears ) -1,
        ListDates = {StartDate..EndDate},
        ConvertedToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "DateKey"}}),
        DateKey = Table.TransformColumnTypes(RenamedColumns,{{"DateKey", type date}}),
        DayID = Table.AddIndexColumn(DateKey, "DayID", 1, 1, Int64.Type),
        YearID = Table.AddColumn(DayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1, Int64.Type),
        //QuarterID = Table.AddColumn(YearID, "QuarterID", each Number.RoundDown(([DayID]-1)/91)+1, Int64.Type),
        MonthID = Table.AddColumn(YearID, "MonthID", each Number.RoundDown(([DayID]-1)/28)+1, Int64.Type),
        WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
        InsertYear = Table.AddColumn(WeekID, "Year", each Date.Year([DateKey]), Int64.Type),
        InsertMonth = Table.AddColumn(InsertYear, "Month", each Date.Month([DateKey]), Int64.Type),
        InsertDayOfMonth = Table.AddColumn(InsertMonth, "Day", each Date.Day([DateKey]), Int64.Type),
        InsertMonthName = Table.AddColumn(InsertDayOfMonth, "Month Name", each Date.MonthName([DateKey]), type text),
        InsertEndOfMonth = Table.AddColumn(InsertMonthName, "End of Month", each Date.EndOfMonth([DateKey]), type date),
        InsertDayName = Table.AddColumn(InsertEndOfMonth, "Day Name", each Date.DayOfWeekName([DateKey]), type text),
        StartOfWeek = Table.AddColumn(InsertDayName, "Start of Week", each Date.StartOfWeek([DateKey], StartOfWeekNum), type date),
        EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], StartOfWeekNum), type date),
        InsertYearFY = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) -1 + [YearID], Int64.Type ),
        InsertPeriodFY = Table.AddColumn(InsertYearFY, "FY Period", each [MonthID] - (( [YearID] -1 ) * 13 ), Int64.Type ),
        InsertWeekFY = Table.AddColumn(InsertPeriodFY, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
        InsertDayOfFY = Table.AddColumn(InsertWeekFY, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type),
        InsertFYPeriod = Table.AddColumn(InsertDayOfFY, "FY & Period", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Period]), 2, "0"), type text),
        InsertFYWeek = Table.AddColumn(InsertFYPeriod, "FY & Week", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Week]), 2, "0"), type text),
        InsertIsAfterToday = Table.AddColumn(InsertFYWeek, "IsAfterToday", each not ([DateKey] <= Date.From(CurrentDate)), type logical),
        InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([DateKey], Day.Monday) > 4 then false else true, type logical),
        InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [DateKey] ), if Holidays = null then type text else type logical)
    in
        InsertIsHoliday
in
fnDateTable

.
The code is based on this article.

The Excelguru Blog – 6 Jan 16

.

You can let me know you’ve appreciated this post by giving it a :heart:

Note. If you always copy the latest version of the M code above, you can take advantage of modifications and/or updates as soon as they become available…

4 Likes

Just adding some other search terms: 13 - 4 week periods power query calendar table, 13-4 week periods calendar function, date table function, fiscal calendar

1 Like

Probably a very simple fix, but how do I modify this to reflect Monday as Day number 0 versus number 1? Or is there a modification to the ‘IsWorkingDay’ code with a “<>” or “=”?
As of right now, the work days show Friday and Saturday as FALSE, but for our reporting it needs to be FALSE for Saturday/Sunday.

Thank you in advance!

2020-07-23 10_39_51-Date Play - Power Query Editor

Hi @Jackie,

Thanks for the feedback!
I’ve updated the M code above, can you give it another go? Thanks

This worked! Thank you.