Lets you slice and group facts by period. It lists every calendar day of the years it covers exactly once, with nothing skipped, and classic DAX time intelligence only works after it has been marked as a date table.
Also called date dimension.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Date table in context, with comparison tables and the common traps.
Terms in this definition
- CALENDAR
Generates a date table as a calculated table: given a start and end date, this DAX function returns a single column, called Date, listing every day in between, including both ends.
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- Time intelligence
Covers DAX functions (TOTALYTD, DATEADD, SAMEPERIODLASTYEAR and others) that shift or accumulate filtered dates. Classic versions require a contiguous, marked date table; calendar-based versions, still in preview, rely on defined calendars.
Related terms
- Auto date/time
With this Power BI Desktop option switched on, each date column in an Import table gets its own concealed date table, unless the column sits on a relationship's many side. That suits basic models, yet it inflates model size and prevents one shared date table from filtering several fact tables.
- Classic time intelligence
The original DAX time functions, like TOTALYTD, which work from the date column of a table marked as a date table. They expect a Gregorian (or shifted Gregorian) calendar with no gaps in the dates and offer no week-level functions, which sets them apart from the calendar-based preview approach.
- Role-playing dimension
One dimension joined to a fact table several times over, each join playing a different role; think of a date table used for ordered, shipped and delivered dates. As semantic models permit just one live relationship per table pair, the extra roles rely on USERELATIONSHIP with relationships left inactive, or on duplicate tables.