Create a date table in Power BI, and when to mark it

Almost every Power BI report groups by year, quarter or month, and almost every model needs year-to-date or "same period last year". Both work best with a date table: one row per day, with the columns people slice by. Microsoft calls it the most common table in analysis, and a date table in Power BI is the first thing to get right in a new model.

Why not just use the date column?

Power BI's auto date/time option quietly creates a hidden date table for every date column in an Import model. That's why a date field shows up with a calendar icon and a Year, Quarter, Month, Day hierarchy. It's fine for quick exploration, but Microsoft recommends your own table for real models:

What a date table must look like

Microsoft's requirements for a table the time intelligence functions can use:

• A column of data type Date (or Date/Time): the date column.
• Unique values, no blanks, and no missing dates between the first and last.
• Full years: from the first day of the first year to the last day of the last year. A year doesn't have to be a calendar year; a fiscal year counts.

Create it with DAX

In Table view (or Report view), select Table tools > New table and paste:

Date =
VAR FirstYear = YEAR ( MIN ( Sales[OrderDate] ) )
VAR LastYear = YEAR ( MAX ( Sales[OrderDate] ) )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( FirstYear, 1, 1 ), DATE ( LastYear, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Quarter", "Q" & QUARTER ( [Date] ),
        "Month number", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "Year month", FORMAT ( [Date], "YYYY-MM" ),
        "Weekday number", WEEKDAY ( [Date], 2 ),
        "Weekday", FORMAT ( [Date], "dddd" )
    )

Change Sales[OrderDate] to your main fact table's date. CALENDAR returns one row per day in a single column called Date, and starting on 1 January and ending on 31 December meets the full-years rule. Because it's a calculated table, it's recalculated on every refresh, so a new year of sales extends it automatically.

CALENDARAUTO instead? CALENDARAUTO() finds the range by itself, in full years, from the dates stored in your model. The catch is that it looks at every date column, so a birth date or a "valid until 9999" date stretches the table by decades. Use CALENDAR with your fact table's dates unless your model only holds dates you want to report on. For a fiscal year ending in June, CALENDARAUTO ( 6 ) ends each year on 30 June.

Or create it in Power Query

If you'd rather keep all tables in Power Query, select Home > New source > Blank query, open the Advanced editor and paste:

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2027, 12, 31),
    Days = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
    Dates = Table.FromList(Days, Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(Dates, {{"Date", type date}}),
    AddYear = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    AddMonthNumber = Table.AddColumn(AddYear, "Month number", each Date.Month([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddMonthNumber, "Month", each Date.ToText([Date], "MMMM"), type text),
    AddQuarter = Table.AddColumn(AddMonth, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text)
in
    AddQuarter

Rename the query Date. The dates here are fixed, so move EndDate on before the year runs out; if your organization already has a date table in a data warehouse, connect to that instead, which Microsoft recommends as the single source of truth.

Relate it and sort it

1. In Model view, drag Date[Date] onto Sales[OrderDate]. Power BI creates a one-to-many relationship, with Date on the "one" side. Do the same for every other fact table's date.
2. Sort the text columns, or months come out April, August, December. Select Month, then Column tools > Sort by Column > Month number; sort Weekday by Weekday number. Each name must map to exactly one number, which these columns do.
3. Use the Date table's columns in visuals and slicers, not the fact table's date. That's what makes one slicer filter every fact table.
4. If your fact table's date includes a time, the relationship only matches midnight. Change the fact column to the Date data type in Power Query, or add a date-only column to relate.

Mark it as a date table

Marking tells Power BI which column is the date column. You must mark it when you use the classic time intelligence functions, which take a date column:

Sales YTD = TOTALYTD ( [Sales], 'Date'[Date] )
Sales last year = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

You also must mark it when the relationship uses a whole-number key such as 20261231 instead of a date column, or when people use Excel PivotTables' advanced date filters on the model.

To mark it, right-click Date in the Data pane and select Mark as date table > Mark as date table (or use Table tools > Mark as date table), then choose the Date column. Power BI checks that the values are unique, not blank and contiguous. Two side effects:

• Power BI removes the hidden auto date/time tables for that table's dates, so visuals built on the old Date Hierarchy fields need rebuilding with the Date table's columns.
• Power BI doesn't create a hierarchy for you. Build your own (Year, Quarter, Month, Date) if you want drill-down.

The newer option: calendar-based time intelligence (preview)

Microsoft now recommends calendar-based time intelligence, still in preview. Instead of marking a table, you define a calendar: you tell Power BI which column is the Year, the Month, the Week and so on. It handles fiscal, retail (4-4-5) and week-based calendars, allows missing dates such as closed weekends, and Microsoft says it performs better.

1. Turn it on in File > Options and settings > Options > Preview features > Enhanced DAX Time Intelligence, then restart Power BI Desktop.
2. Right-click Date and choose Calendar options (or Table tools > Calendar options), then New calendar.
3. Name it (for example Gregorian) and assign columns to categories: Year to Year, Month to Year month, Date to Date. Select Validate data to check them.
4. Refer to the calendar, not the column:

Sales YTD = TOTALYTD ( [Sales], 'Gregorian' )

You don't need Mark as date table with calendars, except in the key and Excel cases above. As a preview, calendars can't be authored in the Power BI service yet and don't work with live-connected or composite models, so stay with the classic approach where that matters.

Turn off auto date/time

With your own date table in place, the hidden tables only add size. Select File > Options and settings > Options, then Current File > Data Load, and clear Auto date/time under Time intelligence. To stop it for new files too, clear Auto date/time for new files on Global > Data Load. Turning it off removes the hidden tables, so fix any visual that still uses an old Date Hierarchy field.

Totals still not what you expect once the dates are right? Our guide on why totals are wrong explains filter context, and building a star schema from messy exports shows where the date table sits in the model.

Sources

• Design guidance for date tables in Power BI Desktop — Microsoft Learn
• Set and use date tables in Power BI Desktop — Microsoft Learn
• Implement time-based calculations in Power BI — Microsoft Learn
• Apply auto date/time in Power BI Desktop — Microsoft Learn
• CALENDARAUTO function (DAX) — Microsoft Learn
• Sort one column by another column in Power BI — Microsoft Learn