Level 2 · Model & DAX

2.5Hierarchies & calendar date table

Goal: Build a standard calendar, linked and hierarchical — to unlock the time intelligence of level 3.

Estimated time: 18 min · self-paced reading · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Temporal analysis requires a dedicated date table: one row per day, continuous, linked to the date of the facts and marked as a date table.
You generate it in DAX: CALENDARAUTO() (scans the model's dates) or CALENDAR(start, end) (explicit bounds).
You sort the months by a numeric column (NumMonth) and build the Year → Quarter → Month → Day hierarchy to make each visual explorable.

Running mini-case — Zineb builds THE calendar of her model

Zineb, a management controller in an agri-food SME in Algiers, wants to track the monthly evolution of revenue, but her months display out of order (April before January). She applies the section: she creates a dedicated date table Dates = CALENDARAUTO(), adds Year, Month, NumMonth and Quarter, sorts Month by NumMonth, links Dates[Date] to Sales[Date] then Mark as date table. Finally she creates the Year → Quarter → Month → Day hierarchy and uses it in her evolution curve: the drill buttons appear. She notes the reflex: turn off Power BI's automatic date hierarchy.

Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 2.5.

1. The dedicated date table

Temporal analysis — evolution, running totals, year-on-year comparison — requires a foundation: the dedicated date table. A calendar dimension with one row per day, continuous over the period, its Year/Quarter/Month/Day columns, marked as a "date table" and linked to the facts. Without it, time intelligence does not work reliably; with it, everything becomes simple. Zineb generates it in DAX: CALENDARAUTO() or CALENDAR(start, end).

2. Sorting the months

Classic trap: "April" sorts before "January" alphabetically. You fix this by sorting the Month column by a numeric column NumMonth (Column tools → Sort by column). Zineb always creates NumMonth = MONTH(Dates[Date]) next to her month label.

3. Hierarchy & a single calendar

Then you build the hierarchy Year → Quarter → Month → Day, which makes each temporal visual explorable (drill down). And a single date table serves the whole model: all temporal analyses plug into it. Zineb turns off Power BI's automatic date hierarchy, which creates a hidden table per column and makes the file heavier.

Common misinterpretation: "Power BI's automatic date hierarchy is enough, the alphabetical order of the months is acceptable, and I can multiply the date tables". No, triple mistake. The automatic one creates a hidden table per date column and makes the file heavier: professionals turn it off in favour of a dedicated table. "April" before "January" distorts any reading: you sort Month by NumMonth. And a single date table must serve the whole model.

Generate, complete, mark: the temporal foundation of analysis.

Text version of the diagram (SEO / screen readers / translation): 1) Zineb, a management controller in Algiers, builds the foundation of her temporal analysis: a dedicated date table, generated in DAX (CALENDARAUTO() or CALENDAR(start, end)), with one row per day, continuous. 2) She completes it (Year, Month, NumMonth, Quarter), sorts the Month column by NumMonth — "April" must not come before "January" — and links Dates[Date] to Sales[Date] before Marking it as a date table. 3) She creates the Year → Quarter → Month → Day hierarchy, uses it in her evolution curve (the drill buttons appear) and turns off the automatic date hierarchy. Lesson: a single date table, continuous and marked, unlocks the time intelligence of level 3.

🔎 Concrete example — 🔎 Concrete example — The calendar in DAX

Zineb generates and completes her date table:

Dates     = CALENDARAUTO()
Year      = YEAR(Dates[Date])
Month     = FORMAT(Dates[Date], "mmmm")
NumMonth  = MONTH(Dates[Date])   // used to sort Month
Quarter   = "Q" & QUARTER(Dates[Date])
→ link Dates[Date] to Sales[Date], then Mark as date table.

Reading: "T" & QUARTER(Dates[Date]) concatenates "T" and the quarter number (T1, T2…).

Lesson: NumMonth exists only to sort Month correctly in the visuals.

Section vocabulary

Date table

Calendar dimension: one row per day, continuous over the period, linked to the date of the facts — the temporal foundation.

Simple example: Zineb creates the Dates table linked to Sales[Date].

What it is not: dates scattered in the fact table, without a calendar.

Reflex: temporal analysis → a dedicated date table.

CALENDARAUTO / CALENDAR

The DAX functions that generate the calendar: CALENDARAUTO() scans the model's dates; CALENDAR(start, end) fixes them.

Simple example: Zineb writes Dates = CALENDARAUTO() to begin.

What it is not: a list of dates entered by hand.

Reflex: clean model → CALENDARAUTO; controlled bounds → CALENDAR.

Mark as date table

The official setting (Table tools) that activates the correct behaviour of the time intelligence functions.

Simple example: Zineb marks Dates as a date table after linking it.

What it is not: an unmarked date table, ignored by time intelligence.

Reflex: date table built → mark it officially.

Hierarchy

Year → Quarter → Month → Day: exploration by levels in the visuals (drill buttons).

Simple example: Zineb explores her curve from year to month by drill down.

What it is not: a single flat level, impossible to explore.

Reflex: make temporal visuals explorable → hierarchy.

Sort by column

You sort the Month column (label) by the MonthNumber column (Column tools → Sort by column).

Simple example: Zineb gets January, February… and not April, January.

What it is not: an alphabetical sort of the months, wrong.

Reflex: displayed month → sort it by its number.

In practice — In practice — Build THE calendar of the model

New table (Table view → New table): Dates = CALENDARAUTO(), like Zineb.
Add the columns: Year = YEAR(Dates[Date]); Month = FORMAT(Dates[Date], "mmmm"); NumMonth = MONTH(Dates[Date]); Quarter = "Q" & QUARTER(Dates[Date]).
Sort Month by NumMonth (Column tools → Sort by column), link Dates[Date] to Sales[Date], then Mark as date table.
Create the hierarchy (right-click on Year → New hierarchy, add Quarter, Month) and use it in your evolution curve: the drill buttons appear.

A standard calendar, linked and hierarchical — the time intelligence of level 3 is unlocked.

Step-by-step script — build the calendar like Zineb (10 min)

1) New table: Dates = CALENDARAUTO(). 2) Add Year = YEAR(Dates[Date]), Month = FORMAT(Dates[Date], "mmmm"), NumMonth = MONTH(Dates[Date]) and Quarter = "Q" & QUARTER(Dates[Date]). 3) Sort Month by NumMonth (Column tools → Sort by column). 4) Link Dates[Date] to Sales[Date], then Mark as date table. 5) Create the Year → Quarter → Month hierarchy and test the drill down in a curve. 6) Turn off the automatic date hierarchy in Options.

If stray dates (1900, birth dates) linger in the model, prefer CALENDAR(start, end) to CALENDARAUTO().

Copy-paste template — my DAX calendar

Dates     = CALENDARAUTO()
Year      = YEAR(Dates[Date])
Month     = FORMAT(Dates[Date], "mmmm")
NumMonth  = MONTH(Dates[Date])
Quarter   = "Q" & QUARTER(Dates[Date])
[ ] Sort Month by NumMonth   [ ] Link Dates[Date] → Sales[Date]
[ ] Mark as date table   [ ] Hierarchy Year → Quarter → Month → Day

Generate the calendar

Zineb writes Dates = CALENDARAUTO(): one row per day covering the model's dates.

Sort the months

Zineb sorts Month by NumMonth: January, February, March… and not April, January.

Mark the table

Zineb links Dates to the sales then Marks it as a date table: time intelligence activates.

Explore by hierarchy

Zineb drags the Year → Month hierarchy into a curve: the drill down reveals each level.

✓ Best practices

Create a single date table, continuous and officially marked.
Sort the months by a numeric column ("Sort by column").
Build the Year → Month hierarchy to make the visuals explorable.

✗ Common pitfalls

Settling for the automatic date hierarchy (hidden table per column).
Forgetting to sort Month by NumMonth (wrong alphabetical order).
Using CALENDARAUTO with stray dates (1900, birth).

Key takeaways

A dedicated date table, continuous, officially marked: non-negotiable for temporal analysis.
Sorting the months goes through a numeric column: "Sort by column" is the reflex.
The Year → Month hierarchy makes each temporal visual explorable (drill down).
A single date table for the whole model: all temporal analyses plug into it.

48h challenge — apply it tomorrow

Build Zineb's calendar: create Dates = CALENDARAUTO(), add Year/Month/NumMonth/Quarter, sort Month by NumMonth, link Dates[Date] to Sales[Date] and Mark the table as a date table. Finish with a Year → Month hierarchy tested in a curve.

Section exit checklist

I generate a date table (CALENDARAUTO or CALENDAR).
I sort the months by NumMonth and I mark the table as a date table.
I build a Year → Month hierarchy and I explore in drill down.

Self-assessment 1-5: do I build a complete calendar without forgetting the sort?Self-assessment 1-5: did I link and mark my date table?

← Previous : Introduction to DAX: SUM, AVERAGE, COUNT (V3)

Frequently asked questions

Is Power BI's automatic date hierarchy not enough?

It gets you by on a small file, but it creates a hidden date table per date column (which makes the file heavier), and allows neither a custom sort of the months nor clean temporal analysis. Professionals turn it off (Options → Data load → Auto date/time) in favour of a dedicated, unique date table that they control entirely.

CALENDARAUTO or CALENDAR: which to choose?

CALENDARAUTO() scans all the dates present in the model and generates a calendar that covers them: handy and with no setting. CALENDAR(DATE(2024,1,1), DATE(2026,12,31)) fixes explicit bounds. You prefer CALENDAR when stray dates (a birth date, a 1900 value) linger in the model: CALENDARAUTO would then generate a huge calendar and make the file heavier.

Check your understanding

What does time intelligence (evolution, running totals, year-on-year) require?

Additional resources (optional, to go further)

📘

Date table (Microsoft Learn)

learn.microsoft.comEN

Videos "Power BI date table & hierarchies"

YouTubeEN

Level 2 examFresh, harder questions to test what you've learned