Latest Enterprise DNA Initiatives


Basic Date Table (Power Query M function)

Hi all,

There have been requests for a basic Date table that includes week logic centered around the start- / end of week dates AND/OR fiscal weeks. Both will start on the selected Start of week day but the first creates weeks of 7 days when crossing over into the next calendar year and the second weeks with <= 7 days around each fiscal year start and -enddate, as illustrated below.
.

.
Copy this M code script into a new blank query.

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional StartOfWeekDay as number, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
    StartOfWeekDayNum = if List.Contains( {0..6}, StartOfWeekDay ) then StartOfWeekDay else 1,
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, 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),
    InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
    InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical),

    InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),

    InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
    InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text),
    InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text),
    InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date),

    InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date], StartOfWeekDayNum ) + WDStart, Int64.Type),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
    InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text),
  
    InsertWeekStart = Table.AddColumn(InsertDayInitial, "WeekStarting", each Date.StartOfWeek( [Date], StartOfWeekDayNum), type date),
    InsertWeekOffset = Table.AddColumn(InsertWeekStart, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], StartOfWeekDayNum))-Number.From(Date.StartOfWeek(CurrentDate, StartOfWeekDayNum)))/7, type number),
    InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], StartOfWeekDayNum) < Date.From(Date.EndOfWeek(CurrentDate, StartOfWeekDayNum)), type logical),
    InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], StartOfWeekDayNum), type date),

    AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") 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),
    AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number),
    AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type number),
    AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
    FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
    InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1) ),
    AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn( 
      Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}), 
        "FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
        "FWStartDate", each  Date.AddYears(Date.StartOfWeek( [Date], StartOfWeekDayNum), 1)),
        {"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
        {"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
        "Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
        "Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
        "AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
      ),
    MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
    ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
    AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1  then Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0"), type text),
    InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1  then Date.Year([Date])+1 else Date.Year([Date])) * 10000 + [Fiscal Week] * 100,  Int64.Type),
    
    InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 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),
    InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
    InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

    CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)),
    CurrentISOyear = CurrentDateRecord{0}[ISO Year],
    CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
    CurrentYear = CurrentDateRecord{0}[Year],
    CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
    CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
    PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
    CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
    CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
    CurrentFW = CurrentDateRecord{0}[FWeeknYear],
    InsertFYoffset = Table.AddColumn(InsertDayType, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
    InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
    InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
    InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
    InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
      ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
          Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"}, 
          Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
          , {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
    InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
    RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"})
  in
    RemoveToday, documentation = [
    Documentation.Name =  " fxCalendar", 
    Documentation.Description = " Date table function to create a calendar", 
    Documentation.LongDescription = " Date table function to create a calendar", 
    Documentation.Category = " Table", 
    Documentation.Version = " 1.05: Changed Fiscal Period type to number",
    Documentation.Source = " local", 
    Documentation.Author = " Melissa de Korte", 
    Documentation.Examples = { [Description =  " See: https://forum.enterprisedna.co/t/basic-date-table-power-query-m-function/19055", 
      Code = " Optional paramters: #(lf)
      (FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf) 
      (Holidays) Select a query (and column) that contains a list of holiday dates #(lf) 
      (StartOfWeekDay) Set start of week day by entering a value between 0-6, where 0 = sunday. If omitted weeks start on monday. #(lf)
      (WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)
      #(lf)
      Important to note: #(lf)
      [Fiscal Week] starts on a the selected weekday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)
      [IsWorkingDay] does not take holiday dates into account  #(lf)
      [IsBusinessDay] does take optional holiday dates into account  #(lf)
      [IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years", 
      Result = " " ] }
    ] 
  in 
  Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))

.
Parameters explained:
Once you’ve invoked this function query, a new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish these elements.

image

  1. Name of the query that was invoked
  2. Startdate (required parameter, needs to be of a type date)
  3. Enddate (required parameter, needs to be of a type date)
  4. FYStartMonthNum (optional parameter of type number)
  5. Holidays (optional parameter of type list)
  6. StartOfWeekDay (optional parameter of type number, where 0=Sunday … 6=Saterday)
  7. WDStartNum (optional parameter of type number, change weekday number from 0…6 to 1…7)
    .

Links to related topics:

Want to create your own Date table?
Keep these requirements in mind for Time Intelligence functions to work properly:

  • Must have a column of data type date (or date/time )—known as the date column
  • The date column must contain unique values
  • The date column must not contain BLANKs
  • The date column must not have any missing dates
  • The date column must span full years. Where a year isn’t necessarily a calendar year (January-December). And cover all dates from the earliest- to the last date present in your dataset.
  • The date table must be marked as a date table

.
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…

I hope you’ll find it useful and if there are any questions, just reach out and let me know! :wink:

All the best,
Melissa

7 Likes

Marking this topic as closed.

Hi @Fcovey,

I noticed you’re using the basic date table… There was a type mismatch which now has been resolved, thanks for reporting it! You can copy an updated version of the basic date table above (from the initial post).

For future reference I’ll leave a link to your post below.

I hope this is helpful.

Melissa, thank you so much!

1 Like