Time intelligence

What is time intelligence?

Time intelligence is the group of DAX functions that shift and compare calculations across dates. With them you answer the questions every business asks: how are we doing year-to-date, how does this month compare to the same month last year, what does a three-month rolling average look like.

Instead of hand-building date maths, you write one measure that says "the same thing, but over this period", and Power BI works out which dates belong to that period from the filter context of the visual.

The functions you reach for most

• Year-to-date and running totals. TOTALYTD and DATESYTD accumulate a value from the start of the year to the date in context. There are matching quarter and month versions.

• Comparing to a prior period. SAMEPERIODLASTYEAR and the more general DATEADD shift the current dates back by a year, a quarter, a month or a day, so you can put this period next to the last one.

• Rolling windows. DATESINPERIOD and DATESBETWEEN return a stretch of dates, which you feed into CALCULATE to build a rolling 7-day or 3-month average.

• Opening and closing balances. Functions like CLOSINGBALANCEMONTH take the value at the last date of a period, which suits stock levels and account balances that should not be summed across days.

A short year-over-year measure shows the shape. It reuses an existing measure, evaluated over last year's dates.

Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Notice the function is handed a column from a dedicated Date table, not from the sales table. That is not a style choice; it is what makes time intelligence work.

Why a proper date table matters

Almost every time-intelligence function assumes a real date table sitting behind your model: one row per day, no gaps, unique dates, at day level. Microsoft's guidance is explicit that you should never point these functions at a date column inside a fact table, and that the classic functions throw an error if there are missing dates between the first and last.

The reason is mechanical. A function like DATESYTD needs to walk a continuous run of dates to know which ones fall in the year so far. If January to March is missing from your calendar, year-to-date has holes and the numbers quietly go wrong. A dedicated date table also gives you year, quarter, month and weekday columns to slice and group by.

You can build a date table in Power Query, or generate one in DAX with CALENDAR or CALENDARAUTO. When you use the classic time-intelligence functions, you also need to tell Power BI which table it is, using Mark as date table, so the engine treats it as the calendar for the model.

Classic versus calendar-based time intelligence

For years there was one approach, now called classic time intelligence: mark a date table, then use functions like SAMEPERIODLASTYEAR against its date column. It is quick to set up and fits Gregorian and shifted-Gregorian calendars, such as a fiscal year that starts on 1 July. It does not handle week-based calculations well, and it needs that unbroken date range.

Power BI has since added a newer, calendar-based approach, released as the Enhanced DAX Time Intelligence preview. You define one or more calendars on a table, which brings better handling of non-standard calendars, week-based periods, and in some cases faster queries. It takes a little more setup. For most models today the classic approach is still the common starting point, with the calendar-based option worth knowing about as it matures.

What to watch out for with time intelligence

• No date table, or the wrong one. Pointing a function at a date column in the fact table is the most common cause of results that look almost right but are not. Use a separate date table and, for classic functions, mark it.

• Gaps in the calendar. A date table that skips weekends or stops at today breaks year-to-date and prior-period logic. Fill the range from the first day of the earliest year to the last day of the latest.

• Filtering the fact date instead of the date table. Slicers and relationships should run through the date table. If a visual filters on the fact table's date, the time-intelligence functions and the visual can disagree.

• Reusing measures, not rewriting logic. Wrap an existing measure in CALCULATE with a time-intelligence filter rather than re-deriving the calculation. It keeps the numbers consistent across the semantic model and the report.