Date Tables

Date tables are one of the most important dimensions in a Power BI data model.

They provide the calendar structure required for time-based analysis.

A date table allows users to analyze data by:

Year
Quarter
Month
Week
Day
Fiscal periods

Example:

              DimDate
                 |
                 |
DimCustomer --- FactSales --- DimProduct

The date table filters the fact table through a relationship.

Why Use a Date Table?

Power BI can automatically create hidden date tables when using date columns.

However, professional Power BI models use dedicated date tables.

Benefits include:

Consistent date filtering
Better DAX calculations
Time intelligence support
Fiscal calendar support
Reusable date logic

Date Table Structure

A typical date table contains:

DimDate

DateKey
Date
Year
Quarter
Month
Month Number
Week
Day

Example:

Date	Year	Month	Quarter
2026-01-01	2026	January	Q1
2026-02-01	2026	February	Q1
2026-03-01	2026	March	Q1

Here is what each part of that structure is for, on a few real rows:

Anatomy of a date table

DimDate, four days around the fiscal year boundary

1Date	2Key	Year	3Month	#	4FY	P
2026-06-29	20260629	2026	JunJune	6	FY2026	12
2026-06-30	20260630	2026	JunJune	6	FY2026	12
2026-07-01	20260701	2026	JulJuly	7	FY2027	1
2026-07-02	20260702	2026	JulJuly	7	FY2027	1

5Same eight days, two ways to build the table

CALENDAR(start, end)

Sat27

Sun28

Mon29

Tue30

Wed1

Thu2

Fri3

Sat4

Dates taken from FactSales

Sat27

Sun28

Mon29

Tue30

Wed1

Thu2

Fri3

Sat4

6One DimDate, two relationships to FactSales

Order (active)	Ship (inactive)	Amount
Jun 29 FY2026	Jun 30 FY2026	$200
Jun 30 FY2026	Jul 2 FY2027	$500
Jul 1 FY2027	Jul 3 FY2027	$300

By order date

FY2026$700

FY2027$300

By ship date

FY2026$200

FY2027$800

Date — one row per day, unique, no blanks. This is the column you pick in Mark as date table, and the one time intelligence functions like TOTALYTD(…, DimDate[Date]) take.

DateKey — the same day as a yyyymmdd integer, for fact tables that store DateKey instead of a date. It's the relationship column, not a replacement for Date.

Month — text, so on its own it sorts alphabetically (April, August, December…). Set Sort by column to Month # once and every visual sorts January to December.

Fiscal columns — computed from Date, side by side with the calendar ones. On Jul 1 the fiscal year turns over to FY2027 and the period resets to 1, while Year stays 2026.

Continuous — a table built from the dates that happen to have sales has holes on every day with none (5 of 8 here). Mark as date table rejects a Date column with gaps, and DATEADD or SAMEPERIODLASTYEAR have no row to land on. CALENDAR() over a fixed range has every day.

Active vs inactive — only one relationship between two tables can be active. The $500 order placed Jun 30 and shipped Jul 2 counts in FY2026 by order date, and moves to FY2027 when a measure switches to the ship date with CALCULATE([Total Sales], USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])).

Date Keys

Many models use a numeric date key.

Example:

Fact tables store the key:

FactSales

DateKey
ProductKey
SalesAmount

The relationship:

DimDate

DateKey

     |
     |

FactSales

DateKey

connects dates to transactions.

Creating a Date Table in DAX

Want the finished version instead of building it column by column? The Date Table Generator writes the whole table in DAX or Power Query M, including fiscal, week, and day-of-week columns.

Example:

DimDate =
CALENDAR(
    DATE(2020,1,1),
    DATE(2030,12,31)
)

Additional columns can be added:

Year =
YEAR(DimDate[Date])

Month =
FORMAT(
    DimDate[Date],
    "MMMM"
)

Month Number =
MONTH(DimDate[Date])

Mark as Date Table

After creating a date table:

Select the table in Power BI.
Choose Table tools.
Select Mark as date table.
Choose the Date column.

This tells Power BI the table represents the official calendar.

Date Hierarchies

Date tables often contain natural hierarchies.

Example:

Year
 |
Quarter
 |
Month
 |
Day

Users can drill down:

2026
 |
Q1
 |
January
 |
January 15

Time Intelligence

Date tables enable common DAX calculations.

Example:

Total Sales

Total Sales =
SUM(FactSales[SalesAmount])

Year-to-Date Sales

Sales YTD =
TOTALYTD(
    [Total Sales],
    DimDate[Date]
)

The calculation automatically responds to date filters. For prior year, growth, and rolling measures on top of the date table, see the Time Intelligence Generator.

Fiscal Calendars

Many businesses do not follow January to December calendars.

Examples:

Manufacturing fiscal years
Retail calendars
4-4-5 calendars

A date table can include:

Fiscal Year
Fiscal Quarter
Fiscal Period

Example:

Date	Fiscal Year	Period
2026-07-01	FY2027	Period 1

Multiple Date Relationships

Fact tables may contain multiple dates.

Example:

FactSales

OrderDateKey
ShipDateKey
DeliveryDateKey

A single date table can support all of them.

Example:

          DimDate

             |
             |

       FactSales

Order Date
Ship Date
Delivery Date

Only one relationship is active.

Other relationships can be activated with DAX.

Date Table Best Practices

Follow these guidelines:

Create one official date table.
Use it across all fact tables.
Include future dates for planning.
Add fiscal calendar columns when needed.
Mark it as a date table.
Sort month names by month number.

Common Date Table Mistakes

Avoid:

Using Automatic Date Tables

Problems:

Creates hidden tables
Duplicates logic
Makes models harder to maintain

Sorting Months Alphabetically

Incorrect:

April
August
December
February

Correct:

January
February
March

Use:

Month Name

sorted by

Month Number

Date Table Checklist

A good date table should:

Contain continuous dates
Have a unique date column
Connect to fact tables
Support time intelligence
Include business calendar requirements

Next Steps

Continue learning Power BI modeling:

Measures

Advanced Topics:

Slowly Changing Dimensions
Bridge Tables
Many-to-Many Relationships
CALENDAR() vs CALENDARAUTO() — why CALENDARAUTO() can silently widen this date table
Calculated Tables — the general mechanism CALENDAR() builds a date table through

Building date-derived columns directly in Power Query instead of DAX? See Working with Dates in Power Query.

A date table doesn't have to relate to a fact table to be useful — Build an Earned Value Management Dashboard uses a disconnected date table to project planned value over time.

Date Tables

On this page