Date Table
Power BI · Data Modeling
Theory
A date table is a dimension with one row per day across the whole period of your data, plus columns such as Year, Quarter, Month and Month Number. It must contain every date without gaps, have a Date column of the date data type with unique values, and be marked as a date table. You relate 'Date'[Date] one-to-many to each date column in the fact tables, such as Sales[OrderDate].
Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR rely on this continuous calendar. You can build it in DAX with CALENDAR or CALENDARAUTO, in Power Query, or import it from the warehouse. For example, CALENDAR(DATE(2024,1,1), DATE(2024,12,31)) returns 366 rows because 2024 is a leap year. Turning off the Auto date/time option is recommended once you have your own date table, so Power BI stops creating a hidden date table for every date column.
Why we use it
Without a proper date table, year-to-date, previous-year and month-level slicing are unreliable or impossible, and each date column gets its own hidden calendar that bloats the model.
When to use it
Create one for every model that analyses data over time, and use it for all date slicing. Only skip it for models with no date analysis at all.
How it works
- Model has date columns
- Create the calendar with CALENDAR or Power Query
- Add Year, Quarter, Month and Month Number columns
- Mark as date table and set Sort by column
- Does Sales have dates outside the calendar range? Yes: Extend the start/end dates. No: Relate 'Date'[Date] to Sales[OrderDate].
- Time intelligence ready
Syntax
DAX:
CALENDAR(<start_date>, <end_date>)
CALENDARAUTO([fiscal_year_end_month])
ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)
Mark: Table tools → Mark as date table → choose 'Date'[Date]
Off: File → Options and settings → Options → Data Load → Auto date/timeExample
Date =
ADDCOLUMNS(
CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"Month Number", MONTH([Date]),
"Quarter", "Q" & QUARTER([Date])
)
// Then: Mark as date table, sort Month by Month Number,
// relate 'Date'[Date] 1 → * Sales[OrderDate]Key points
- One row per day, no gaps, unique Date column
- Mark it as a date table so time intelligence works reliably
- Sort the Month name column by Month Number
- Relate it to every date column in fact tables
- Turn off Auto date/time when you have your own date table