Extended Date Table (Power Query M function)

Hi everyone,

To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! :wink:
.

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list ) as table =>
  let
    FYStartMonth = try if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1 otherwise 1,
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "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.Monday), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "ISO Weeknumber", each
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),
    BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOday = Table.AddColumn(InsertISOyear, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
    InsertCalendarWk = Table.AddColumn(InsertISOday, "Week & Year", each if [ISO Weeknumber] <10 then Text.From([ISO Year]) & "-0" & Text.From([ISO Weeknumber] ) else Text.From([ISO Year]) & "-" & Text.From([ISO Weeknumber] ), type text ),
    InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),

    InsertMonthnYear = Table.AddColumn(InsertWeeknYear , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY"&(if FYStartMonth =1 then Text.End(Text.From([Year]), 2) else if [MonthOfYear]>=FYStartMonth then Text.From(Number.From(Text.End(Text.From([Year]), 2))+1) else Text.End(Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear]>=FYStartMonth then [MonthOfYear]-(FYStartMonth-1) else [MonthOfYear]+(12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "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),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),

    InsertWeekOffset = Table.AddColumn(InsertIsHoliday, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/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),

    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentFiscalYear = IdentifyCurrentDate{0}[Fiscal Year], 
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each Number.From(Text.Range([Fiscal Year],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)), type number),

    InsertCompletedWeek = Table.AddColumn(InsertFYoffset, "WeekCompleted", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "MonthCompleted", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertChangedType = Table.TransformColumnTypes(InsertCompletedQuarter,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}})
  in
  InsertChangedType
in
fnDateTable

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

17 Likes

Just adding some other search terms: calendar table, calendar function

Hi Melissa:

This really cool and thoughtful of you for sharing!

Best regards,

Bill S

1 Like

Thanks @Melissa, you are awesome :slight_smile:
Have a beautiful day
Najah

1 Like

Agree just amazing work.

Any chance we can get a separate Extended Time Table seeing we are suggested to split out time from date. Would be great if we could get an Extended Time Table to go with it. :slight_smile:

Hi David,

Makes sense, you can find it here:

Thank you again Melissa!

Very good Melissa. I’ve just voted for a new virtual pbi training about all this. I really hope you guys manage to get some time to do it.
Thank you so much

Hi Pedro,

As a matter of fact @BrianJ and I are collaborating on a Time Intelligence series as we speak and of course we will also be covering the Date table. So if you haven’t already, subscribe to the Enterprise DNA YouTube channel so you won’t miss a thing! :wink:

3 Likes

oooohhhh that is soooooooo cooooll!!! and yes i am a dna youtube subscriber for quite a while now. And it’s a great idea to teach also about TI measures concerning last week or weeks or month or quarter regarding the beginning of the year.
Thanks a lot Melissa. That is great news!!!

Muchas gracias Melisa, Awesome

This is very helpful Melisa, you are a rockstar here.

@Melissa I need to adjust the ISO Year column to match our “week-years.”
Our weeks run Sunday - Saturday, and our billing “months” end on the last Saturday in the calendar month.
I have been trying to adjust the code in the InsertWeekNumber= line to match this calendar. Can you explain out the last ‘else’ line in that code?
else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Sunday)+1)+10)/7), type number),
Specifically - Why is it adding 1, then 10 before dividing by 7?
Learning a ton here. . .
Thank you.

Hi @kjssdca,

Thanks for your question.

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, you have to add the optional first-weekday value (= Day.Monday) and change the numbering so it runs between 1 and 7.
This explains the +1

The +10 / 7 is part of the algorithm for calculating the week number, you can find more on that here.

If you need assistance creating a customized week number, please create a new topic. You’ll find that there’s an increasing number of active members here who’d like to help out. :wink:

1 Like