Create a Reliable Power BI Date Table with DAX

A Power BI date table should contain one row per date, cover every date required by the model, and have a unique, non-null, contiguous date column. Build the table first, then add attributes, sort labels correctly, create the relationship and validate time calculations.

Use fixed dates when the reporting boundary is a business decision. Use values derived from the model when the table should expand with the data.

Create the table

In Power BI Desktop, select Table tools > New table and enter a DAX expression. This fixed example is easy to verify:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2027, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "Day of Week Number", WEEKDAY ( [Date], 2 ),
    "Day of Week", FORMAT ( [Date], "DDD" )
)

Change the boundaries to suit the report. Do not blindly copy a decade from somebody else's example. Include future dates only when forecasts, budgets or scheduled events require them.

CALENDARAUTO() is another option. It derives a range by scanning date values in the model, with rules described in Microsoft's DAX reference. That convenience can also include dates from columns you did not intend to drive the reporting calendar. Inspect the result.

Sort labels with numeric columns

Text month names sort alphabetically unless you tell Power BI otherwise. Select Month, choose Sort by column, then select Month Number. Do the same for weekday labels and their numeric weekday column.

For reporting across several years, use a year-month key or date at month grain. Sorting all values called "Jan" by the number 1 does not create chronological order across years.

Create the relationship

Relate Date[Date] to the fact table's date column. Check that the fact column contains dates at compatible grain. A timestamp such as 2026-08-24 14:30 will not match a date value at midnight until it is converted or a date-only column is created.

Normally the date table sits on the one side of a one-to-many relationship and filters the fact table. Confirm rather than assuming Power BI inferred the relationship you wanted.

Mark the table when your time-intelligence route requires it

For classic time-intelligence functions, select the table and use Table tools > Mark as date table, then choose the date column. Power BI validates uniqueness, nulls and continuity.

Microsoft's newer calendar-based time intelligence is a separate feature with different setup and preview considerations. Do not mix instructions from the two approaches. If production stability matters, check the current feature status and tenant policy before adopting a preview.

Test a calculation

Given an existing measure called [Sales Amount], a classic prior-year measure can be written as:

Sales Amount Previous Year =
CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Put Year and Month on a visual and check boundary periods, missing dates and partial years. A formula returning a value does not prove the comparison is meaningful.

Sources

Check the model, not just the formula

The Microsoft 365 Report Builders Space is for practical questions about models, measures and report behaviour. Share a sanitised model description, not confidential data.