How to create a date table in Power BI with a ready-made Excel file

If you don’t have access to a date table in a source database or if you’re new to Power BI and aren’t that comfortable with programming in DAX or M then sign up to my mailing list and get the link to download a ready-made excel date table. You can import this into Power BI, mark as a date table and start using it straight away.

What does the excel date table contain?

The default file contains a date table for the years 2000-2023 with 22 additional columns of information (the date range can be changed within the excel file). Here’s a snapshot of the first week’s data:

The excel file has an instructions tab that explains how to change the date range, the financial year starting month, the week day starting day, which days are classed as weekends and how to add public holidays.

How to set up the date table in Power BI

Follow these steps to import the excel table into Power BI and set it up as a date table:

  1. Firstly, turn off the auto date/time detection in Power BI
    1. File menu – Options and settings – Options
    2. Select Data Load under the CURRENT FILE heading
    3. Untick Auto date/time
  2. Load the ‘Date table’ tab in the excel file into Power BI using the Get Data button
  3. Set the table as an official Date Table
    1. Click on the table name in the fields panel (right hand side of screen)
    2. Click on the 3 dots that appear
    3. Select Mark as Date Table
    4. Select the date column from the drop down list
  4. Delete any of the calendar columns that you don’t require in your report. This is recommended for performance reasons.
  5. And now your table is ready to be used!

List of table columns with descriptions of content.

DateDate in UK format (can be changed to US format in Power BI)
DayDay of year number starting from Jan 1st
Day of weekDay of week name – Monday, Tuesday etc
Day of week shortShort day of week name – Mon, Tue, Wed etc
Day of week letterFirst letter of the day of week name – M, T, W etc
Day of week numberWhere Monday = 1 and Sunday = 7
WeekendYes for weekends – No for weekdays
Week NumberWeek number where 1 = first week of January and 52 = last week in December
MonthMonth name
Month NumberMonth number where where January = 1 and December = 12
Month YearShort month name and year i.e Jan 2020
Year Month NumberYear and month number i.e 202001
Month ShortShort month name – Jan, Feb, Mar etc
Month LetterFirst letter of the month name
QuarterQuarter based on calendar year – Q1 = Jan, Feb, Mar
YearYear
Year ShortLast 2 numbers of the year
Financial Month NumberMonth Number where April = 1 and March = 12
Financial QuarterQuarter based on financial year – Q1 = Apr, May, Jun
Financial YearFinancial Year
Financial Year DisplayYears spanning the financial year i.e 2019-2020
Public HolidayYes/No flag to show whether the day is a public holiday – only Christmas day is included in the sample but you can add all public holidays to the look up tab in the excel file.
Public Holiday DescriptionDescription of the public holiday if the date is a public holiday i.e Christmas Day (will have n/a if it’s not a public holiday)

Processing…
Success! You're on the list.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: