Launch Deal · 50% off 1st month ·

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.

See plans

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.