Algorithm for Calendar Query

I’m trying to adapt the Extended Date Table code which results in an ISO Calendar (weeks start on Monday) to make a Custom Calendar with weeks starting on Sunday instead. Here’s the code:

WeekNumber = Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Sunday)+1)+10)/7), type week number)

@Melissa wrote: “By default the Power Query Date.DayOfWeek function returns a number between 0 and 6 starting on Sunday. To return the weekday number according to ISO8601 rules, the coding adds the optional first-weekday value (= Day.Monday) and change the numbering so it runs between 1 and 7. This explains the +1.” This is good, because this matches our weeks, so I understand I need to eliminate the +1.

“The +10 / 7 is part of the algorithm for calculating the week number, you can find more on that here, scroll down to “Algorithms” section.”

This is the part that I’m having difficulty adapting to our custom calendar. Here’s how our calendar works:

  • Weeks run Sunday – Saturday
  • Billing months end on last Saturday of the month
  • So for instance, Dec 27 2020 is actually Day 1, Week 1 of Billing Year 2021
    How do I change the algorithm to accommodate that?

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.

  1. FirstDate, enter the starting date for the calendar
  2. LastDate, enter the ending date for the calendar
  3. Calendar-Base_BS, part 1 for the date table
  4. Calender-FiscalPeriods_BS, part 2 for the date table
  5. 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 =>
        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)


Hi @kjssdca, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

@Melissa, you are a rockstar - thanks so much for doing the heavy lifting here!

@kjssdca, glad I could help.