Power BI Date Table Not Working? 8 Time-Intelligence Checks

When a Power BI date table is not working, validate the calendar
before rewriting the time-intelligence measure. The date column must be
unique, nonblank, and continuous for classic time intelligence; its
range must cover the fact data; and an active relationship must connect
the correct date role.

These eight checks isolate the failure.

Check 1: The date key is
unique

A date dimension should contain one row per date.

Compare:

Date Table Rows =
COUNTROWS ( 'Date' )

Distinct Dates =
DISTINCTCOUNT ( 'Date'[Date] )

The counts should match. Duplicates often appear when a source
calendar contains one row per date and business unit, or when a
Date/Time value was formatted to look like a date without removing the
time.

Use a dedicated date-only key for the relationship.

Check 2: There are no
blanks or gaps

For classic time-intelligence functions, the date column should
have:

• no BLANK values;
• every date in the range;
• one value per day; and
• full years where the design requires them.

Microsoft’s
date-table design guidance lists unique values, no BLANKs, no
missing dates, and full-year coverage among the core requirements for a
classic date table.

Generate a basic calendar:

Date =
CALENDAR (
    DATE ( 2024, 1, 1 ),
    DATE ( 2026, 12, 31 )
)

Or derive the range deliberately from your data pipeline. Avoid an
unexplained CALENDARAUTO range in production because hidden
date columns can extend it.

Check 3: The range
covers the fact table

A perfect calendar still fails if it ends before the latest
transaction or starts after the earliest comparison period.

Create:

First Calendar Date =
MIN ( 'Date'[Date] )

Last Calendar Date =
MAX ( 'Date'[Date] )

First Sales Date =
MIN ( Sales[OrderDate] )

Last Sales Date =
MAX ( Sales[OrderDate] )

For prior-year comparisons, the Date table must include the
prior-year dates even if the visual begins later.

If SAMEPERIODLASTYEAR alone returns blank, use the prior-year
checklist.

Check
4: The relationship columns have matching data types

The relationship often looks correct but compares:

Date[Date] = 2026-06-19 00:00:00
Sales[OrderDateTime] = 2026-06-19 14:42:07

Those values are not equal.

Create a date-only column during data preparation:

Order Date =
DATE (
    YEAR ( Sales[OrderDateTime] ),
    MONTH ( Sales[OrderDateTime] ),
    DAY ( Sales[OrderDateTime] )
)

Power Query or the source system is generally a better place to
normalize this at scale.

Also check that both columns use compatible Date data types, not text
on one side and Date on the other.

Check 5: The
intended relationship is active

A fact table can have multiple dates:

• order date;
• ship date;
• due date;
• payment date.

Only one Date relationship may be active between the same tables. A
measure using the active Order Date path will not automatically answer a
Ship Date question.

Use:

Shipped Sales =
CALCULATE (
    [Sales Amount],
    USERELATIONSHIP (
        Sales[ShipDate],
        'Date'[Date]
    )
)

The USERELATIONSHIP
guide explains how to activate an existing alternate date role for
one measure.

If the dimension does not filter the fact table at all, follow the
broader relationship
troubleshooting order.

Check 6: The visual uses
the Date table

This is a frequent source of valid-looking but wrong results.

Use:

Date[Year]
Date[Month]
Date[Date]

on axes, slicers, and filters—not:

Sales[OrderDate].[Year]

The fact column’s automatic date hierarchy may come from a hidden
auto date/time table. Your measure can shift filters on the explicit
Date table while the visual groups by a different hidden calendar.

Microsoft
explains that auto date/time creates hidden tables for eligible date
columns. Mixing those hidden calendars with an explicit date table
produces multiple date contexts that look similar in the field list.

Hide fact-table date fields from report view once the explicit Date
table is established, except where users genuinely need the raw
timestamp.

Check
7: The table is configured for the time-intelligence mode you use

Power BI’s current date-table interface distinguishes classic and
calendar-based time intelligence.

For classic functions, marking a date table remains important in
required scenarios. Microsoft’s
current date-table documentation notes that a custom table used with
classic time intelligence must be marked as a date table, while
calendar-based time intelligence has different configuration
behavior.

Do not mix a tutorial written for one mode with a model configured
for the other without checking the function signature and calendar
setup.

For classic configuration:

1. select the Date table;
2. choose Table tools → Mark as date table;
3. select the unique date column; and
4. resolve every validation error rather than bypassing it.

Check
8: Other filters are not removing the comparison period

The calendar can work while a report filter eliminates the required
dates.

Examples:

• the page is filtered to Year = 2026 while a prior-year measure needs
  2025;
• a relative-date filter restricts the visual;
• a disconnected period selector applies an incompatible range;
• a fact-table date filter intersects the date-table filter; or
• the measure clears or replaces the wrong date column.

Expose the visible range:

Debug Date Range =
FORMAT ( MIN ( 'Date'[Date] ), "yyyy-mm-dd" )
    & " → "
    & FORMAT ( MAX ( 'Date'[Date] ), "yyyy-mm-dd" )

Then display fact-row counts for the current and shifted periods.

A known-good test measure

Begin with:

Sales Amount =
SUM ( Sales[SalesAmount] )

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

Create a simple table using only Date[Year],
[Sales Amount], and [Sales Previous Year].

If that works, reintroduce:

1. month;
2. slicers;
3. alternate date roles;
4. calculation groups;
5. the original visual.

The first reintroduced element that breaks the result identifies the
layer to inspect.

Why the date table filters
nothing

Use:

Visible Sales Rows =
COUNTROWS ( Sales )

Place it beside Date[Year].

If the row count is identical for every year:

• the relationship may be missing or inactive;
• its direction may not reach Sales;
• the visual may use a field from another calendar;
• the relationship key may include timestamps; or
• the measure may remove Date filters.

Do not solve this with FILTER ( ALL ( Sales ), ... ).
Repair the model path.

Why months sort
alphabetically

A Month Name text column needs a numeric sort column:

Month Number =
MONTH ( 'Date'[Date] )

Select Month Name and choose Sort by column → Month
Number.

Across multiple years, use a unique Year-Month field:

Year Month Sort =
YEAR ( 'Date'[Date] ) * 100
    + MONTH ( 'Date'[Date] )

A label of “January” alone cannot distinguish years.

Frequently asked questions

Do I always
need to mark a date table in Power BI?

It depends on the time-intelligence mode and model design. Classic
time intelligence with a custom date table requires marking it in
relevant scenarios; calendar-based time intelligence uses its calendar
configuration. Follow the documentation for the mode you actually
use.

Why does my date
relationship show blanks?

Check timestamps, data types, fact dates outside the calendar range,
and unmatched keys. A displayed date format can hide a time
component.

Can I use the
fact-table date on the axis?

You can, but it may invoke a hidden auto date/time table and bypass
the explicit calendar used by the measure. Prefer fields from the shared
Date dimension.

Where can I test a
clean calendar model?

Use the DAX
playground with one Date table, one active fact relationship, and a
base measure before adding alternate date roles.