2.3 — Create a Common Date Table
PL-300 Domain 2 · Skill 2.3
What You Need to Know
Almost every model needs one shared date table related to every fact table's date column. It gives consistent years, quarters, months and fiscal periods across facts, and DAX time intelligence functions (lesson 2.6) rely on it.
Requirements for a date table
- One row per day, with no gaps and no duplicates
- The date column has the Date (or Date/Time) data type and no blanks
- It covers complete years for every date in the facts (time intelligence assumes full years)
Ways to create one
| Method | Example | Notes |
|---|---|---|
DAX CALENDAR |
Date = CALENDAR(DATE(2022,1,1), DATE(2026,12,31)) |
Explicit start and end |
DAX CALENDARAUTO |
Date = CALENDARAUTO(6) |
Scans all date columns in the model and returns whole years. The optional argument is the fiscal year's last month (6 = fiscal year ends in June) |
| Power Query | List.Dates turned into a table, then add columns with the Date tools |
Easy to reuse in other models, for example from a dataflow |
| From the source | A date dimension in the data warehouse or lakehouse | Best in enterprise models, where it's shared and owns fiscal logic |
Then add columns such as Year, Quarter, Month Number, Month Name, Year-Month, Fiscal Year and Weekday, for example with ADDCOLUMNS or calculated columns, and sort Month Name by Month Number (lesson 2.1).
Mark as date table
In Table view, select the table and choose Table tools > Mark as date table, then pick the date column. Power BI validates it (unique, no blanks, contiguous).
Marking the table:
- Lets time intelligence functions work even when the relationship uses an integer key such as DateKey 20260131 instead of a date column
- Removes the automatic date hierarchy for that table, so you build your own Year > Quarter > Month hierarchy
Auto date/time
By default, Power BI Desktop creates a hidden date table for every date column in the model. That's convenient in tiny models, but:
- It bloats the model (one hidden table per date column)
- Each table only covers its own column's dates, so dates aren't shared across facts
- It doesn't support fiscal years
Turn it off in File > Options and settings > Options > Current file > Data load > Auto date/time (and under Global for new files) once you have a proper date table.
Key Concept: Build one contiguous date table, relate it to every fact's date column, mark it as a date table, and turn off Auto date/time. Multiple date roles (order, ship) reuse it as in lesson 2.2.
Exam Traps
- A date table with gaps (only days that had sales), which breaks time intelligence
- Leaving Auto date/time on in a large model with many date columns
- Expecting CALENDARAUTO to stop at the last sale date (it returns whole years)
- Forgetting to mark the table when the relationship uses an integer date key
- Using a separate date table per fact table, so one slicer can't filter both facts
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Create date tables in Power BI Desktop, Microsoft Learn
- Set and use date tables in Power BI Desktop, Microsoft Learn
- Auto date/time in Power BI Desktop, Microsoft Learn
- CALENDARAUTO function (DAX), Microsoft Learn