Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 56 ยท Data Modelling

Date Tables and Time Intelligence Setup

Build a Power BI date table with CALENDAR or CALENDARAUTO, mark it as the date table, add hierarchies and a fiscal calendar, and sort month names.

Almost every report asks questions about time: sales this month, compared with last month, year to date, by quarter. DAX has a family of time-intelligence functions that answer these questions, but they only work reliably when the model has a proper date table. Without one, "same period last year" returns blanks, months sort alphabetically (April, August, December ...) and fiscal years are impossible.

In this lesson you will learn why a date table is needed, how to create one with CALENDAR or CALENDARAUTO, how to mark it as the date table, how to add the columns people need (year, month, quarter, fiscal year), and how to make month names sort in calendar order. The time-intelligence formulas themselves come in Time Intelligence: YTD, Prior Year and Moving Averages. The date table is the dimension that connects to your facts in the way described in Relationships, Cardinality and Filter Direction.

Power BI Desktop cannot run on this website, so the DAX below is read-only, and the menu steps are described in words. The runnable examples are Python stand-ins that use pandas.date_range to build the same kind of table, with the same columns and gaps checks. The real work is done by DAX or Power Query inside Power BI.

Why a separate date table

A fact table with an OrderDate column has dates only on days when something happened. Time intelligence needs a continuous sequence of days with no gaps: to compute "same period last year" for 15 March, the engine looks up 15 March of the previous year, and that day must exist, even if there were no sales.

A good date table has:

  • One row per day, with no missing dates and no duplicates.
  • Whole years: starting on 1 January of the first year and ending on 31 December of the last (or on the fiscal year boundaries).
  • A Date column of type Date (not date-time) that is unique.
  • Useful descriptive columns: year, quarter, month number, month name, day of week, and so on.
  • A relationship to every fact table's date column (usually one-to-many from the date table).

What a date table needs

Every day No gaps and no duplicates.
Whole years Start on day one, end on the last day.
Unique dates A Date column of type Date.
Related Linked to each fact table's date.
1 / 2

Creating a date table with DAX

DAX can create a whole calculated table from a formula. You go to Table view or Modeling, choose New table, and type a formula.

CALENDAR takes a start and an end date:

TEXT
DimDate = CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) )

CALENDARAUTO scans the dates already in the model, finds the earliest and latest year, and returns whole years that cover them:

TEXT
DimDate = CALENDARAUTO ()

You can pass a month number (1-12) to CALENDARAUTO to say that the fiscal year ends in that month, for example CALENDARAUTO ( 6 ) for a year ending in June. A caution: CALENDARAUTO looks at every date column in the model, including columns like a birth date or an 1900-01-01 placeholder, which can stretch the calendar by decades. If you see a calendar that starts in 1900, check your data, or use CALENDAR with fixed or computed limits.

A common compromise is to compute the limits from the fact table:

TEXT
DimDate =
CALENDAR (
    DATE ( YEAR ( MIN ( FactSales[OrderDate] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( FactSales[OrderDate] ) ), 12, 31 )
)

Adding the descriptive columns

The result of CALENDAR has only the Date column. You add the rest as calculated columns (they are computed once at refresh and are the right choice for dimension attributes):

TEXT
Year = YEAR ( DimDate[Date] )
Quarter = "Q" & QUARTER ( DimDate[Date] )
Month Number = MONTH ( DimDate[Date] )
Month Name = FORMAT ( DimDate[Date], "MMMM" )
Month Short = FORMAT ( DimDate[Date], "MMM" )
Year Month = FORMAT ( DimDate[Date], "YYYY-MM" )
Weekday Number = WEEKDAY ( DimDate[Date], 2 )
Weekday Name = FORMAT ( DimDate[Date], "dddd" )
Is Weekend = IF ( WEEKDAY ( DimDate[Date], 2 ) > 5, TRUE (), FALSE () )

WEEKDAY(date, 2) numbers the days from Monday = 1 to Sunday = 7. The FORMAT results are text, and text sorts alphabetically, which leads to the next problem.

Sort by column

Put Month Name on an axis and Power BI shows April, August, December, February ... in alphabetical order. The fix is Sort by column. In Table view, select the Month Name column and, on the Column tools ribbon, choose Sort by column and pick Month Number. The names now follow the numbers 1 to 12.

The same trick works for Weekday Name with Weekday Number, and for Month Short or Year Month. A column that you sort by has to map one-to-one: each month name must correspond to exactly one month number. A text column like Year Month (2025-01) already sorts correctly as text, so it needs no helper.

Hide the helper columns (Month Number, Weekday Number) after setting the sort, so report authors see only the friendly names.

Mark as date table

Once the table exists and is related to your facts, tell Power BI that it is the date table: right-click the table in the Data pane, choose Mark as date table, and select the Date column. Power BI checks that the column is unique, complete and of date type.

Marking tells the engine that time-intelligence functions can rely on the table's Date column, even when you filter the table in unusual ways. If the check fails, fix the cause: duplicates, missing days, or a date-time column that contains times.

Auto date/time is a global setting (under Options, Data Load) that creates a hidden date table for every date column in the model. It is convenient for a first chart, but it multiplies hidden tables, cannot be customised and does not give you a fiscal calendar. When you build your own date table, switch it off and use yours.

Hierarchies and fiscal calendars

A hierarchy lets users drill from year to quarter to month to day in a single visual. You create it in Model view or in the Data pane by right-clicking a column such as Year and choosing Add to hierarchy, then adding Quarter, Month Name and Date. Details are in Model View, Hierarchies, Formatting and Display Folders.

A fiscal calendar starts the year in a month other than January. Suppose the year runs from July to June. Add fiscal columns:

TEXT
Fiscal Year = IF ( MONTH ( DimDate[Date] ) >= 7, YEAR ( DimDate[Date] ) + 1, YEAR ( DimDate[Date] ) )
Fiscal Month Number = MOD ( MONTH ( DimDate[Date] ) - 7, 12 ) + 1
Fiscal Quarter = "FQ" & ROUNDUP ( [Fiscal Month Number] / 3, 0 )

Fiscal years are usually named by the year in which they end, so 1 July 2025 belongs to fiscal year 2026. Check the convention your company uses and write it into the column name or a description. Sort Fiscal Month Name by Fiscal Month Number so July comes first.

Stand-in 1: build the table with date_range

The first stand-in builds a small calendar with pandas and checks the properties Power BI looks for: unique, continuous, complete years. The month name sort problem is shown with the numeric helper that fixes it.

Python Stand-in: a calendar table and its checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first three checks match what Mark as date table validates. The two month lists show why Sort by column with a number is needed.

Stand-in 2: a fiscal calendar and a gap in the facts

The second stand-in adds fiscal columns for a July-to-June year, and shows why a continuous calendar matters: the fact table has no rows for several days, but the date table still lists them, so a report can show a zero or blank for them.

Python Stand-in: fiscal year and missing days

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Fiscal year 2025 ends on 30 June and fiscal year 2026 starts on 1 July, so the same calendar splits the sales into two fiscal years. The facts table alone could not list the days without sales; the date table can.

Common mistakes

  • Using the fact table's date column as the axis for time intelligence. Always use the Date column of the date table.
  • A date table with a date-time column. If times are present, the values are not unique per day. Use the Date type.
  • Incomplete years. A calendar that stops in October makes year-to-date and prior-year logic misleading in the last months.
  • Several date tables by accident, from Auto date/time and your own. Decide on one approach.
  • Forgetting Sort by column and publishing reports with alphabetical months.
  • Mark as date table skipped. It is a one-click step that prevents surprising results later.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does time intelligence need a continuous date table?

    Time-intelligence functions shift sets of dates. If days are missing, the shifted period is incomplete, so results are blank or wrong.
  2. 2. What is the difference between CALENDAR and CALENDARAUTO?

    CALENDAR needs explicit limits. CALENDARAUTO scans all date columns in the model and returns whole years that cover them, with an optional fiscal year end month.
  3. 3. How do you make Month Name sort January to December?

    Text sorts alphabetically. Sort by column tells Power BI to order the names by the numbers, which map one-to-one to the months.
  4. 4. Which condition does Mark as date table check?

    Power BI validates that the chosen column has unique dates and no gaps, so time-intelligence functions can rely on it.
  5. 5. A fiscal year runs July to June and is named by the year it ends in. Which fiscal year is 1 July 2025 in?

    The year that began on 1 July 2025 ends on 30 June 2026, so with this naming convention it is fiscal year 2026.

Summary

  • Time intelligence needs a date table: one row per day, no gaps, whole years, a unique Date column of type Date.
  • Create it with CALENDAR (explicit range) or CALENDARAUTO (whole years from the model), or in Power Query or the warehouse.
  • Add year, quarter, month number, month name and weekday columns; fix ordering with Sort by column.
  • Right-click the table and Mark as date table; switch off Auto date/time when you use your own.
  • Fiscal calendars are extra columns; choose and document a naming convention.
  • The Python stand-ins mirror the columns and checks; the real table is built in Power BI.

Next, tidy up how the model looks and behaves in Model View, Hierarchies, Formatting and Display Folders.