3.1.4. Creating a Common Date Table

💡 First Principle: Time is the dimension almost every business question uses, and time intelligence functions need a reliable calendar to calculate "same period last year" or "year to date." A single, complete, shared date table gives every fact the same calendar, so a slicer on Year filters sales, budget, and inventory consistently.

Auto date/time (Options > Data Load) creates a hidden date table for every date column in the model. It's convenient for quick exploration but bloats the model with many hidden tables, can't be shared across facts, and can't hold fiscal periods or holidays. Best practice for any real model, and the usual exam answer, is to turn off auto date/time and create a dedicated date table.

Ways to create it:

• DAX calculated table: CALENDAR(DATE(2020,1,1), DATE(2027,12,31)) returns a contiguous range between two dates; CALENDARAUTO() scans the model's date columns and returns full years covering them (optionally with a fiscal year-end month). Add columns with ADDCOLUMNS or calculated columns (Year, Month Number, Month Name, Quarter, Fiscal Year).
• Power Query: generate a list of dates (List.Dates), convert to a table, and add columns with the Date transformations. Useful when the same calendar should be reused across models or come from a central source.
• A source table: many organizations maintain a date dimension in the data warehouse, which is often the best choice because fiscal calendars and holidays are maintained centrally.

Requirements to Mark as date table (Table tools > Mark as date table, choosing the date column): the column must be of Date (or Date/Time) type, contain unique values, have no blanks, and be contiguous (no missing days). Covering full calendar years is strongly recommended, because time intelligence functions such as year-to-date assume complete years. Marking the table tells Power BI to use it for time intelligence and removes the need for auto date/time on related columns.

Relate the date table's date (or integer DateKey) column to each fact's date column with one-to-many, single-direction relationships. One date table, many facts: that's what makes it a common date table.

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

⚠️ Exam Trap: A date table with gaps (for example, built from distinct order dates, so days without orders are missing) can't be marked as a date table and breaks time intelligence. Build the calendar from a contiguous range (CALENDAR, CALENDARAUTO, or List.Dates), never from the distinct values of a fact column.

Reflection Question: A model has Sales and Budget facts, each with its own date column, and auto date/time is on. Describe two problems this causes and the configuration that fixes both.