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.

Hi Melissa,

i just found this table very useful and i’m trying to adptapt it to mey needs but am sttruggling a bit.
i have the need for a 13 period calendar that starts on the 1st of april and ends on the 31st of march. my weekday start is saturday, P2 to P12 last 28 days, P1 will start on 1/4 and ends on friday closest to 28 days and P13 will start on saturday the day after p12 end and will finish on 31st march. can you help with this?

Hi @lagp78,

I might be able to assist, but please create a new thread.
And include an xlsx that clearly illustrates all rules and exceptions that need to be taken into account.

Cheers.

Companies using a 13 Period calendar often have a 53 week year every 6 years in order to account for the calendar only having 364 days. Is there a way to account for this?