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:
- Firstly, turn off the auto date/time detection in Power BI
File menu — Options and settings — Options
Select Data Load under the CURRENT FILE heading
Untick Auto date/time - Load the ‘Date table’ tab in the excel file into Power BI using the Get Data button
- Set the table as an official Date Table
Click on the table name in the fields panel (right hand side of screen)
Click on the 3 dots that appear
Select Mark as Date Table
Select the date column from the drop down list - Delete any of the calendar columns that you don’t require in your report. This is recommended for performance reasons.
And now your table is ready to be used!
List of table columns with descriptions of content.
- Date: Date in UK format (can be changed to US format in Power BI)
- Day: Day of year number starting from Jan 1st
- Day of week: Day of week name — Monday, Tuesday etc
- Day of week short: Short day of week name — Mon, Tue, Wed etc
- Day of week letter: First letter of the day of week name — M, T, W etc
- Day of week number: Where Monday = 1 and Sunday = 7
- Weekend: Yes for weekends — No for weekdays
- Week Number: Week number where 1 = first week of January and 52 = last week in December
- Month Number: Month number where where January = 1 and December = 1
- Month Year: Short month name and year i.e Jan 2020
- Year Month Number: Year and month number i.e 202001
- Month Short: Short month name — Jan, Feb, Mar etc
- Month Letter: First letter of the month name
- Quarter: Quarter based on calendar year — Q1 = Jan, Feb, Mar
- Year Short: Last 2 numbers of the year
- Financial Month Number: Month Number where April = 1 and March = 12
- Financial Quarter: Quarter based on financial year — Q1 = Apr, May, Jun
- Financial Year: Financial Year
- Financial Year Display: Years spanning the financial year i.e 2019–2020
- Public Holiday: Yes/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 Description: Description 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)
Originally published at http://bi-jo.com on December 1, 2020.
