Hi @kjssdca,
I found an article on Fiscal Saturday calendars by Ken Puls and applied those principles.
It can definitely use some more refining but its a good starting point - I think. Let me know if the week logic is to your satisfaction and/or you need help creating additional date dimensions.
-
FirstDate, enter the starting date for the calendar
-
LastDate, enter the ending date for the calendar
-
Calendar-Base_BS, part 1 for the date table
-
Calender-FiscalPeriods_BS, part 2 for the date table
-
Dates, result query loaded to the model
I hope this is helpful. Here’s my sample file.
eDNA - Fiscal Saturday Calendar.pbix (128.6 KB)
.
Or extended and combined in single a function:
let fnDateTable = (StartDate as date, EndDate as date ) as table =>
let
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
CalendarBaseCreation = Table.FromColumns({{Number.From(StartDate)..Number.From(Date.AddMonths(EndDate, 1))}}, {"Date"}),
CalendarBase = Table.TransformColumnTypes(CalendarBaseCreation,{{"Date", type date}}),
FiscalPeriodCreation = Table.FromColumns({{Date.AddMonths(Date.EndOfMonth(StartDate),-1) } & List.Generate(() => [Start = Date.EndOfMonth(StartDate), i=1, MY = Start],
each Date.AddMonths([Start], [i]-1) <= Date.EndOfMonth(Date.AddMonths(EndDate, 1)),
each [MY = Date.AddMonths([Start], [i]), i = [i]+1, Start = [Start]],
each [MY])}),
InsertFiscalME = Table.AddColumn(FiscalPeriodCreation, "Fiscal ME", each [Column1] - #duration(Date.DayOfWeek([Column1], Day.Saturday), 0, 0, 0), type date),
InsertEndOfYear = Table.AddColumn(InsertFiscalME, "End of Year", each Date.EndOfYear([Fiscal ME]), type date),
InsertDayOfWeek2 = Table.AddColumn(InsertEndOfYear, "Last Saterday", each Date.DayOfWeek([End of Year], Day.Saturday), Int64.Type),
InsertFiscalYE = Table.AddColumn(InsertDayOfWeek2, "Fiscal YE", each [End of Year] - #duration([Last Saterday],0,0,0), type date),
FiscalPeriods = Table.SelectColumns(InsertFiscalYE,{"Fiscal ME", "Fiscal YE"}),
Source = Table.NestedJoin(CalendarBase, {"Date"}, FiscalPeriods, {"Fiscal ME"}, "Tbl", JoinKind.LeftOuter),
ExpandedSource = Table.ExpandTableColumn(Source, "Tbl", {"Fiscal ME", "Fiscal YE"}),
SortAscending = Table.Sort(ExpandedSource,{{"Date", Order.Ascending}}),
FillUp = Table.FillUp(SortAscending,{"Fiscal ME", "Fiscal YE"}),
FilterEndDate = Table.SelectRows(FillUp, each [Date] <= EndDate),
InsertYear = Table.AddColumn(FilterEndDate, "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayOfWeek", each Date.DayOfWeek([Date]), Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek( [Date], Day.Sunday), type date),
InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
InsertFiscalMonth = Table.AddColumn(InsertMonthEnding, "Fiscal Month", each Date.Month([Fiscal ME]), Int64.Type),
InsertFiscalYear = Table.AddColumn(InsertFiscalMonth, "Fiscal Year", each Date.Year([Fiscal YE]), Int64.Type),
BufferTable1 = Table.Buffer( InsertFiscalYear[[Fiscal ME], [Date], [DayOfWeek]] ),
InsertWeekOfMonth = Table.AddColumn( InsertFiscalYear, "Week of Month", each let myDates = Table.SelectRows(BufferTable1, (BT)=> BT[Fiscal ME] = _[Fiscal ME] and BT[Date] <= _[Date] and BT[DayOfWeek] =5 ) in if Date.DayOfWeek(#date(Date.Year([Fiscal YE]), 1, 1))<5 then try List.Count(myDates[Date])+1 otherwise 1 else List.Count(myDates[Date]), type number ),
BufferTable2 = Table.Buffer ( InsertWeekOfMonth[[Fiscal YE], [Date], [Week of Month]] ),
InsertFiscalWeek = Table.AddColumn(InsertWeekOfMonth, "Fiscal Week", each Table.RowCount(Table.Group( Table.SelectRows(BufferTable2, (BT)=> BT[Fiscal YE] = _[Fiscal YE] and BT[Date] <= _[Date] )[[Week of Month]], {"Week of Month"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local )), type number ),
InsertWeeknYear = Table.AddColumn(InsertFiscalWeek, "FW & FY", each Text.From( [Fiscal Year]) & " - " & Text.PadStart( Text.From( [Fiscal Week]), 2, "0"), type text),
InsertMonthnYear = Table.AddColumn(InsertWeeknYear, "FM & FY", each Text.From( [Fiscal Year]) & " - " & Text.Start( Text.Proper( Date.MonthName([Fiscal ME])), 3), type text),
InsertIsAfterToday = Table.AddColumn(InsertMonthnYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if [DayOfWeek] = 5 then false else if [DayOfWeek] = 6 then false else true, type logical),
InsertWeekOffset = Table.AddColumn(InsertIsWorkingDay, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Sunday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Sunday)))/7, type number),
InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number),
InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number),
InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number)
in
InsertYearOffset
in
fnDateTable
