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, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1,
    //the WDStartNum parameter allows you to set Weekday numbering running from 0-6 or 1-7 but first day of the week will remain Monday
    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),
    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]) + WDStart, 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 Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), 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 [MonthOfYear] >= FYStartMonth 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),
    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 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),

    //InsertDayOffset = Table.AddColumn(InsertDayType, "DayOffset", each Number.From([Date] - CurrentDate), type number),  //if you enable DayOffset, don't forget to adjust the PreviousStepName in the next line of code.
    InsertWeekOffset = Table.AddColumn(InsertDayType, "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)),
    CurrentYear = IdentifyCurrentDate{0}[Year],
    CurrentMonth = IdentifyCurrentDate{0}[MonthOfYear],
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - 
      (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
    RemoveToday = if EndDate < CurrentDate then Table.SelectRows(InsertFYoffset, each ([Date] <> CurrentDate)) else InsertFYoffset,
    InsertCompletedWeek = Table.AddColumn(RemoveToday, "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

.
Don’t want to invoke a function?
With the minor changes depicted below the output of the M code will be a table.

And there’s also a video taking you through the depicted steps, you can find that here:.


.
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

.

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

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…

47 Likes
Creating a dynamic Start-/EndDate for the Date table
Updating the date table
New Cheat Sheet for Extended Date Table
Add Fiscal Year + Fiscal Month to Date table Query
Help with adapting Current Staff calculations
Period Slicer for custom Periods
Date Table - how do I change it to get TODAY's date and avoid manual updates
Calendar Table with DAX function
Number of Employees at a particular Branch overtime
Adding my own company holidays from Excel table
Dynamic date table creation code
How to calculate the sales during the first 6 months after accounts created
Add Fiscal Year + Fiscal Month to Date table Query
Display only two rows month wise in Table Visual with DAX
Multiple Date table
Events in Progress
Fiscal Year -> Calendar Year
Reporting for every two weeks together for last 3 fortnights
Week number for Qtr
SamperiodlastYear Working Days
Creating Date Table Dimension using only Fiscal Quarter Data
Time Intelligence series by BrianJ and Melissa
DAX Calculate dyanamic measure to compare YoY in graphic chart
I need help getting a ROLL UP SUB TOTALS for a template value
Algorithm for Calendar Query
Help with sum between two dates
Previous 12 months sales by month based on selected slicer values
Cumulative sales through same week last year
Negative sales by customer
Trying to create a Relationship but could not get '1 to Many'
Compare Tables Data Model - Guidance
Power BI Challenge 8 - Jira IT Service Desk Analysis Entry from Mosbehi (non-member)
Sort Area Chart by date like "01 Jan"
Compare most recent June to previous June
Missing sales in the first week of each year
Power BI Challenge 7 - Purchases, Inventory And Sales Entry from Greg
Power BI Challenge 7 - Purchases, Inventory And Sales!
Average Daily Price in a Month for a Specific Product
Do I really need a full year in my Date table?
Non-Standard Time Intelligence
Power BI Challenge 6 - Insurance Complaints Entry from Greg
Power BI Challenge 6 - Insurance Complaints
Grouping Sat-Sun-Mon into Monday
Rolling 12 Financial Periods
SelectedValue (slicer[TYPE]) Today, Yesterday, MTD, etc
DSO Calculation - referencing calculated table
Show prior 3 Months Cumulative Total month over month
How to Default Slicer End Date to always be last date in data
Change Day in The Week Number to 1 for Sunday through to Saturday = 7

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

2 Likes

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:

1 Like

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

Thank you @Melissa for sharing! Date Table is getting better and better :+1:

Your code has opened my appetite to keep the “EndDate” dynamic, so it will always be the last day of the current year. That would save me from updating/maintaining the Dates table in all my reports next year(unless there is another way which I am not aware of :smiley: )

Using your M code, I have:
1- Removed “EndDate as date” from the first line and;
2- Added another line “EndDate = Date.EndOfYear(CurrentDate),” after the “Current Date”.

1 Like

Hi @Hesham,

Within this category you can also find a topic on creating a dynamic Start- and Enddate.

.
In addition there will also be a video on that topic in the Time Intelligence series Brian and I are doing. We’ll add a link here as soon as it’s released to the Enterprise DNA Channel.

.
Thanks for sharing your method

3 Likes

Thanks @Melissa!!!

This post came just right in time!!

:+1: :+1: :+1:

Hi Melissa,
The optional parameters work fine, would it be possible to add a selection parameter to show Monday as Weekday 1 please, so users can select either 0 or 1.
Paul

Hi Paul,

Thanks for your question!

:thinking: That sounds like a nice addition indeed. So added the optional WDStartNum parameter this allows you to set the [DayOfWeek] numbering from either 0-6 or 1-7. However if omitted, the standard numbering will be 0-6.

Keep in mind that this doesn’t affect the start of the week day, that will always remain Monday as is custom to this Calendar and the ISO week logic.

2 Likes