Date tables: the rules time intelligence depends on

Year-to-date, same period last year and rolling averages are among the most requested numbers in any report. In DAX they are short formulas, but they rely on one thing being right: the date table.

What a date table is

A date table is a dimension with one row per calendar day and columns that describe each day: year, quarter, month name, month number, week, and any fiscal periods your organisation uses. The fact tables connect to it through their date columns.

The rules

Microsoft’s guidance sets out the requirements for a table to work as a date table:

1. It must have a column of the date data type.
2. That column must contain unique values: one row per day.
3. The dates must be contiguous, with no gaps, even on days with no sales.
4. It should cover whole years, from 1 January of the first year to 31 December of the last year in your data (or whole fiscal years).

A table with one row per month, or with gaps on weekends, breaks these rules and makes time intelligence functions return wrong or empty results.

Mark it as a date table

In Power BI Desktop, select the table and choose Mark as date table, then pick the date column. Marking it tells Power BI which column to use for time intelligence, validates the rules above, and turns off the automatic hidden date tables for that table. A useful side effect: when you filter the date column inside CALCULATE, the other filters on the date table are removed, which is what year-to-date logic needs.

Building one

You can build a date table in DAX:

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

Or in Power Query, or in your data warehouse. Teams that share models across reports usually prefer the warehouse or Power Query, so the same calendar is used everywhere.

Remember to set Sort by column on the month name, using the month number. Otherwise months sort alphabetically: April, August, December.

Time intelligence once the table is right

Sales YTD = TOTALYTD ( [Total Sales], Dates[Date] )
Sales LY  = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Dates[Date] ) )

Both functions take the date column of the date table, never the date column of the fact table.

When the model has two dates

Orders often have an order date and a ship date. Only one relationship between the fact table and the date table can be active. Keep the other one inactive and switch to it in a measure with USERELATIONSHIP, or add a second date table when users need to slice by both dates at once.

Sources

• Set and use date tables in Power BI Desktop, Microsoft Learn
• Create date tables in Power BI Desktop, Microsoft Learn
• Active vs inactive relationship guidance, Microsoft Learn