A Date Table is a special table that contains a continuous list of dates along with time-related columns like Year, Month, Quarter, and Day. It is mandatory for using Time Intelligence functions such as YTD, MTD, QTD, SAMEPERIODLASTYEAR, etc.
? Key Concepts
Power BI does not automatically understand time
Time intelligence works only with a proper Date Table
Date Table must have continuous dates
One Date Table should be used across the entire model
Date Table must be related to fact tables
? Syntax / Theory
Date Table = Continuous Dates + Calendar Attributes + Relationship
? Method 1: Create Date Table Using CALENDARAUTO()
? View Code Example
// Automatically creates a date table based on data model
DateTable =
CALENDARAUTO()
CALENDARAUTO scans all date columns in the model and generates dates automatically.
? Method 2: Create Date Table Using CALENDAR()
? View Code Example
// Creates date table between fixed start and end dates
DateTable =
CALENDAR(
DATE(2020,1,1),
DATE(2030,12,31)
)
This method gives full control over the date range.
? Add Date Attributes (Columns)
? View Code Example
// Year column
Year = YEAR(DateTable[Date])
// Month number
MonthNo = MONTH(DateTable[Date])
// Month name
MonthName = FORMAT(DateTable[Date],"MMM")
// Quarter
Quarter = "Q" & FORMAT(DateTable[Date],"Q")
? Live Output / Explanation
Each row in the Date Table now represents one calendar date with all required time-based breakdowns.
? Interactive Example (How Power BI Uses Date Table)
When a Date slicer is used, Power BI filters data using the Date Table, not directly from the fact table.
? Use Cases
Year-to-Date (YTD) analysis
Month-on-Month comparison
Quarterly performance tracking
Trend analysis over time
Financial and sales reporting
?️ Date Table Architecture Simulator
Add attributes to see how a flat list of dates becomes a powerful DimDate table.
Date (PK)
Year
MonthName
Quarter
Note: Continuous rows (no gaps) allow Power BI to shift dates for MTD/YTD calculations.