The idea
Time intelligence sounds as if DAX reads your sales and understands time. It does not. Functions
such as TOTALYTD, SAMEPERIODLASTYEAR and DATEADD work on dates, and they get those
dates from one place: a Date table, a calendar with one row per day.
What they do is small. They look at the dates the current cell shows, build a different set of
dates (January 1 up to now, the same days a year earlier, the month before), and put that set into
the filter before the measure runs. The measure itself, [Sales], never changes. Only the days it
is given do.
Figure 1
A calendar built from order dates has holes
| order_date | orders |
|---|---|
| 2024-01-27 | 5 |
| 2024-01-28 | 12 |
| 2024-01-29 | 9 |
| 2024-01-31 | 8 |
| 2024-02-01 | 7 |
| Date | Year Month | Weekday Number |
|---|---|---|
| 2024-01-27 | 2024-01 | 6 |
| 2024-01-28 | 2024-01 | 7 |
| 2024-01-29 | 2024-01 | 1 |
| 2024-01-30 | 2024-01 | 2 |
| 2024-01-31 | 2024-01 | 3 |
| 2024-02-01 | 2024-02 | 4 |
- In 2024, the store took no order on January 30, so that date never appears in
orders[order_date]. The store's 1,095 busy days make a calendar with a hole. - Time intelligence assumes the calendar is complete. A period shifted back a year or forward a month can land on a date that does not exist, and the result is wrong or blank.
- A
'Date'table is built independently, with one row for each of the 1,096 days from 2023-01-01 to 2025-12-31. January 30 is there, with zero sales.
Why it exists
"How are we doing compared with last year?" is the first question a manager asks. In a spreadsheet you answer it by hand: copy the sales, shift a column, subtract. In Power BI you ask for "the same period last year" and it has to know what a period is. Order rows cannot say. A year, a month, a weekday or a holiday is not a property of a sale; it is a property of a day, and the calendar is where days live.
So the model gets one extra table, built for this, and every function that talks about time reads from it. One calendar also means one definition of "month" for every report.
How it works under the hood
Three rules for the calendar
- Continuous. One row for every day, with no gaps, from the first date you need to the last. Build it from a range (the store's is 2023-01-01 to 2025-12-31), not from the dates in your orders.
- Related.
'Date'[Date]has one row per day, so it sits on the 1 side of a relationship toorders[order_date], as the star in the data-modelling chapter does. - Marked. Select the table and use Table tools → Mark as date table, then pick the
Datecolumn. Power BI checks that it holds unique dates, and DAX learns which table is the calendar.
Everything else on the table is for slicing: Year, Quarter, Month Number, Month,
Year Month and Weekday Number. You can add your own as calculated columns, such as a weekend
flag with IF.
Year-to-date, step by step
TOTALYTD is CALCULATE with a prepared set of dates. For the March row it takes the last visible
day, builds January 1 to March 31 and evaluates [Sales] on them. Month by month, the measure
traces a line that restarts every January.
Figure 2
Sales YTD, 2025: a running total that restarts every January
Sales YTD = TOTALYTD([Sales], 'Date'[Date]). January shows January's sales, $150,571, because the year-to-date starts on January 1.- March shows $417,261: January, February and March added. The measure is the same in every cell; the dates it is given grow.
- June crosses the $1 million plan line: $1,026,527 by June 30.
- December shows the whole year, about $2.3 million, and in January the line starts again from zero.
The family has the same shape. DATESMTD builds the days from the 1st of the month. TOTALQTD
does it for the quarter. DATESYTD returns the set of dates so you can use it inside CALCULATE
yourself, which is how a measure like year-to-date last year is built.
Shifting: same period last year
SAMEPERIODLASTYEAR('Date'[Date]) takes the visible days and moves each one back a year.
For the March 2025 row that means March 2024. CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
returns $161,477 where [Sales] returns $166,787, so the growth is
DIVIDE([Sales] - [Sales PY], [Sales PY]) = 3.3%. DATEADD('Date'[Date], -1, MONTH) shifts by
any step you name, which is how a previous-month measure is made.
Two things keep these safe. Use DIVIDE, because the first year of data has no previous year and
the division must show blank, not an error. And write the growth once with variables (VAR), so
the shifted measure is calculated a single time.
Why "marked" matters
The matrix row also brings its own date filters, Year Month = 2025-03. Whether the new dates
replace these or sit next to them depends on the mark.
Figure 3
How TOTALYTD builds its filter for the 2025-03 row
- Cell's filters Year = 2025, Year Month = 2025-03
- Last visible date March 31, 2025
- Build the dates January 1 to March 31
- Put them in the filter on 'Date'[Date]
- Evaluate [Sales] on those days
- The row brings its own filters: the Year slicer and the Year Month on the matrix row.
TOTALYTDlooks at the dates that are visible and takes the last one, March 31.- From that date it builds the days from January 1 of the same year up to it.
- With the calendar marked as the date table, the new dates replace the row's date filters. The sum then runs on January to March: $417,261.
- Unmarked, the row's
Year Month = 2025-03survives next to the new dates. They intersect and leave only March: $166,787, the same asSales.
When the table is marked, DAX knows the calendar and clears every date filter before it applies the new ones. When it is not marked, the old filters stay, and a year-to-date for March is built from January to March and March: just March. The telltale sign is that the Total row is right and every other row equals its own month. That is the whole of the "why is my YTD just the month?" bug.
Other calendars
A fiscal year that starts in July still uses the same table. TOTALYTD takes an optional third
argument, the year-end date, as in TOTALYTD([Sales], 'Date'[Date], "6/30"), and the running total
restarts the day after. Columns such as a fiscal year or a "last 12 months" flag are one more
calculated column each on 'Date', which is why the calendar is worth owning.
Common misconceptions
"I can use orders[order_date] as my calendar"
A column of order dates has a row per order, not per day, and missing days (January 30, 2024 here).
Time functions need unique, complete dates. 'Date'[Date] is the column to give them. If your model
has one, check it is the column Mark as date table points at.
"Auto date/time makes a Date table unnecessary"
Auto date/time quietly creates a hidden calendar for every date column. It works for a one-off
chart, but it gives each date column its own calendar, cannot hold your own columns like a fiscal
year or a weekend flag, and cannot be shared between tables. Most modellers turn it off and build one
'Date' table.
"The time functions look for the sales of last year"
They look for days. The function hands the engine a set of dates, and the engine filters the
facts through the relationship in the usual way. If a relationship to the calendar is missing or
inactive, the dates are built correctly and never reach the sales, so the measure returns the same
number as Sales or blank.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Right. With an unmarked table the row's Year Month filter stays and intersects with Jan 1 to Feb 28, leaving just February. Mark the table and the date filters are replaced, which gives $250,474.
Not quite. Month length does not matter; the function adds every day from January 1 to the row's last date.Try another answer.
Not quite. It is the opposite: time intelligence wants the calendar's date column, which holds every day. An order date has holes and is not the marked column.Try another answer.
Not quite. Direction decides how filters travel between tables, not which dates a function builds. The problem is two date filters meeting on the same table.Try another answer.
Lesson done: it is ticked on the map.