Why Create a Date Table in Power BI?

By: Collin Quiring

We have trained thousands of folks on Power BI and have worked with hundreds of companies.  We always recommend a date table be created in Power BI.  And we often are asked why we are so strongly recommending a date table.

Here are some of the answers to that question.

A date table:

• Gives you the ability to organize your data by time – years, quarters, months, weeks, days (or even hours and minutes if you need that much detail)
• Gives you the ability to aggregate data and information by time
• Has a SINGLE record for each and every day
• Has that SINGLE daily record for whatever time period you set (can be 1 month or 50 years)
• Only has to be created one time
  • And can be used in multiple semantic models
  • And can be used as a template
  • And can be shared with other report writers so they don’t have to create one
• Allows for data consistency in analysis
• Allows for the usage of DAX for Time-intelligence functions
  • Such as Year-over-Year information
• Gives you the ability to create Fiscal Year and Fiscal Calendar in addition to the regular calendar dates
• Gives you the ability to create CUSTOMER’S Fiscal Calendar into your date table
• Gives you the ability to create Holiday’s and put them into the calendar – whether for your just your country, or for other countries where you have employees or business
• Gives you the ability to mash up data from multiple data sources
  • Particularly if the Date Field(s) in the sources are the only keys available
• Gives you the ability to create different criteria specific to your organization (ie: set the working days and/or set the “day of the week” number for each day)

Just to clarify though, we do NOT recommend creating a date table in Power BI and then Marking it as a Date Table.  The main reason for this is that when you mark a table as the official “date table” in Power BI the tool then creates quite a few background items for you – creating date hierarchies and determining which fields are date fields.  Normally, we like Power BI doing things in the background for us.  However, in this case, we have found that automatic background work by Power BI tends to make date measures more difficult and the hierarchies do not always appear as expected.  And, we have found the world tends to be more complex than the structure that is created automatically (particularly with Fiscal Calendars).

There are many examples of Date Tables out there if you just search for them.  It is VERY important that you fully understand a date table example if you download one.  You need to be sure to understand if that is a standard calendar or if that is based on a 4-5-4 or 4-4-5 or other calendar method that your organization uses.

What kind of post would this be if we didn’t provide a simple example ourselves for you to use?

To use the following as a Date Table in your own Power BI report(s) all you need to do is create a blank query and then go to the Advanced Editor and paste this entire query into that editor (replacing whatever else is in there already).  This example does NOT have a fiscal year involved.

Once you have pasted this into the Advanced Editor, change the two dates to be the date range that you want.  The StartDate is currently set to 01/01/2018 and the EndDate is set to 12/31/2030.

After changing those dates, select “Done” at the bottom of Advanced Editor and you now have your Date Table.

Copy the code for the Advanced Editor from here to the end of the page.

let
    StartDate = #date(2018,1,1),
    EndDate = #date(2030,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateField"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateField", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateField]), type number),
    #"Change Year to Text" = Table.TransformColumnTypes(#"Inserted Year",{{"Year", type text}}),
    #"Inserted Short Year" = Table.AddColumn(#"Change Year to Text", "Short Year", each Text.End([Year],2)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Short Year",{{"Short Year", type text}}),
    #"Inserted Start of Year" = Table.AddColumn(#"Changed Type2", "Start of Year", each Date.StartOfYear([DateField]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Start of Year]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([DateField]), type number),
    #"Inserted Month 2Digit" = Table.AddColumn(#"Inserted Month", "Full Month", each if [Month]< 10 then Text.Combine({"0", Text.From([Month], "en-US")}) else Text.From([Month],"en-US")),
    #"Changed Type3" = Table.TransformColumnTypes(#"Inserted Month 2Digit",{{"Full Month", type text}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type3", "Month Name", each Date.MonthName([DateField]), type text),
    #"Inserted Month Name Abbrev" = Table.AddColumn(#"Inserted Month Name", "Month Name Abbrev", each Text.Start([Month Name],3)),
    #"Changed Type4" = Table.TransformColumnTypes(#"Inserted Month Name Abbrev",{{"Month Name Abbrev", type text}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type4", "Start of Month", each Date.StartOfMonth([DateField]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([DateField]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([DateField]), type number),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Days in Month", "Quarter", each Date.QuarterOfYear([DateField]), type number),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([DateField]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([DateField]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([DateField]), type number),
    #"Inserted Week Number 2Digit" = Table.AddColumn(#"Inserted Week of Year", "Week Number 2Digit", each if [Week of Year]< 10 then Text.Combine({"0", Text.From([Week of Year], "en-US")}) else Text.From([Week of Year],"en-US")),
    #"Changed Type6" = Table.TransformColumnTypes(#"Inserted Week Number 2Digit",{{"Week Number 2Digit", type text}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type6", "Week of Month", each Date.WeekOfMonth([DateField]), type number),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([DateField]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([DateField]), type date),
    #"Inserted Day" = Table.AddColumn(#"Inserted End of Week", "Day", each Date.Day([DateField]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Day",{{"Day", "Day Number of Month"}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Renamed Columns1", "Day of Week", each Date.DayOfWeek([DateField]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([DateField]), type text),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day Name", "Day of Year", each Date.DayOfYear([DateField]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Day of Year", "Day Name Abbrev", each Text.Start([Day Name],3)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Day Name Abbrev", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Week Day", each if [Day of Week] = 6 then "No" else if [Day of Week] = 0 then "No" else "Yes"),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Week Day", type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Changed Type5", "Work Day Count", each if [Week Day] = "Yes" then 1 else 0),
    #"Changed Type7" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Work Day Count", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type7", "Month Number Name", each Text.Combine({[Full Month], [Month Name]}, "-"), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Month Number Name Abbrev", each Text.Combine({[Full Month], [Month Name Abbrev]}, "-"), type text),
    #"Added Custom1" = Table.AddColumn(#"Inserted Merged Column1", "Today", each DateTime.LocalNow()),
    #"Changed Type8" = Table.TransformColumnTypes(#"Added Custom1",{{"Today", type date}})
in
    #"Changed Type8"
    /* This is a Power BI standard calendar created by EPM Strategy.  There is no warranty with this
and is used as is with user taking all risks.  User should confirm that dates and days match
their organizational calendar. */

Comments