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 
).
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.
.
You can let me know you’ve appreciated this post by giving it a 
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…
