The date table: the quarter of an hour that decides half of your measures

Your fiscal year ends on 30 September. You write a year-on-year comparison measure, it returns a figure, and that figure is wrong. No error, no warning: a plausible number.

Nine times out of ten, the cause is the same: there is no real date table in the model, or it is not marked as such.

What a date table really changes

Power BI automatically creates a date hierarchy for every column of date type. It is convenient in a demo and costly in production: one hidden table per column, no notion of fiscal year, no working day, and time-shifting functions that cannot be optimised.

A dedicated date table settles four things at once.

Time functions work. SAMEPERIODLASTYEAR, DATEADD and TOTALYTD require a table marked as a date table, continuous, with no gaps. Without that, they fall back on a generic calculation, slower, and wrong as soon as the fiscal year does not follow the calendar year.

Axes become shared. Sales, purchases and stock are compared on the same calendar rather than each on its own date column.

Business vocabulary enters the model. Fiscal year, fiscal quarter, ISO week, working day: so many columns that exist once and for all instead of being recalculated in every measure.

Performance follows. A date table is small, a few thousand rows, and the engine knows how to use it to prune the periods it does not need to read.

The table we set up

It fits in a single DAX query, and it covers the cases we come across.

Date =
VAR FiscalYearStart = 10      -- first month of the fiscal year; 1 for the calendar year
VAR StartDate = DATE ( 2018, 1, 1 )
VAR EndDate   = DATE ( 2030, 12, 31 )
RETURN
ADDCOLUMNS (
    CALENDAR ( StartDate, EndDate ),
    "Year",             YEAR ( [Date] ),
    "Month",            MONTH ( [Date] ),
    "Month name",       FORMAT ( [Date], "MMMM" ),
    "Year-month",       FORMAT ( [Date], "YYYY-MM" ),
    "Quarter",          "Q" & QUARTER ( [Date] ),
    "Weekday",          WEEKDAY ( [Date], 2 ),
    "Is working day",   WEEKDAY ( [Date], 2 ) <= 5,
    -- The shifted fiscal year: everything after the first month belongs to
    -- the following fiscal year. This is the line that is almost always missing.
    "Fiscal year",
        IF (
            MONTH ( [Date] ) >= FiscalYearStart,
            YEAR ( [Date] ) + 1,
            YEAR ( [Date] )
        ),
    "Fiscal month",
        MOD ( MONTH ( [Date] ) - FiscalYearStart + 12, 12 ) + 1
)

Three points matter.

The bounds cover the whole history, with no gaps. A table that starts in 2020 when entries go back to 2018 silently breaks the cumulative totals. A table that stops in 2026 breaks the forecasts.

Is working day is a Boolean, not a string. A Boolean compresses to a single bit; “Yes” / “No” takes up far more over several thousand rows, and filters more slowly.

Public holidays are not in this version. They depend on the country and sometimes the region; we load them from a reference table rather than calculating them, because a rule for calculating public holidays is wrong sooner or later.

The three steps that are almost always missing

Mark the table

In Power BI Desktop, select the table, then Mark as date table by designating the Date column. Without this step, the table exists but the time functions do not rely on it.

It is one click, and it is the step most often forgotten.

Relate on the right date

An entry often carries three dates: the invoice date, the posting date and the entry date. They differ, sometimes by several weeks at the end of the fiscal year.

Relate on the one that carries the business meaning, almost always the invoice date for revenue. The other two stay as columns, usable occasionally with USERELATIONSHIP.

Hide the technical column

The Date column is for relationships, not for display. A user who places it in a visual gets one row per day over eight years. Hide it and expose Year, Year-month and Fiscal year.

How to check that the table is right

Three checks, two minutes.

1. No gaps. Compare COUNTROWS of the table with DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ), DAY ) + 1. The two must be equal.
2. The fiscal year switches at the right moment. Filter on 30 September and 1 October: the fiscal year must change between the two.
3. The year-on-year comparison is right. Take a month for which you know last year's figure, and check that SAMEPERIODLASTYEAR returns it.

This third check is the only one that really counts. The first two merely locate the cause when it fails.

What to remember

A date table takes a quarter of an hour and conditions half the measures in a model. It must be continuous, cover the whole history, be marked as a date table, be related on the date that carries the business meaning, and carry your fiscal year if it does not follow the calendar year.

When a year-on-year comparison returns a wrong figure without an error, that is almost always the first place to look.