Lesson 7 of 11 · 9 min · DAX
Time intelligence: year to date, last year and growth
What you will learn
- The requirements for time intelligence to work
- Year-to-date, quarter-to-date and month-to-date measures
- Same period last year and year-over-year growth
- Rolling and moving totals with
DATESINPERIOD - Fiscal years and common failures
In simple words
Business questions are full of time: "sales so far this year", "compared with the same month last year", "the last three months". DAX has ready-made functions for these, called time intelligence. They only work when you give them a proper calendar table with every date, because they move across that calendar to find the right days: "this year up to today", "the same dates, one year back".
Words to know
- Time intelligence: DAX functions that calculate over periods such as year to date or the previous year.
- Date table: a table with one row per day and no gaps, used as the calendar.
- YTD: year to date, from 1 January up to the current date in context.
- YoY: year over year, comparing a period with the same period one year earlier.
Requirements
Time-intelligence functions need:
- A Date table with a continuous range of dates (no missing days) that covers all your data, and a column with unique dates.
- The table marked as a date table (Table tools > Mark as date table), or an active relationship from the fact table's date column to the Date table's date column.
- A relationship from
Date[Date]to the fact table's date column (one-to-many, single direction). - In visuals and measures, use columns from the Date table (
'Date'[Month]), never the date column in the fact table.
If any of these is missing, measures return blank or the same value everywhere, which is the most common time-intelligence problem. Use Date[Date] in the DAX functions below.
The base measure
Total Sales = SUM ( Sales[Amount] )
All the measures below wrap this in CALCULATE with a different set of dates.
Year to date, quarter to date and month to date
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales QTD = TOTALQTD ( [Total Sales], 'Date'[Date] )
Sales MTD = TOTALMTD ( [Total Sales], 'Date'[Date] )
In a monthly chart, Sales YTD shows a running total that restarts each January. The long form shows what is happening:
Sales YTD (long) =
CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )
DATESYTD returns the dates from the first day of the year up to the last date in the current context, and CALCULATE applies them. For a fiscal year ending 31 March, give the year-end date:
Sales Fiscal YTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )
Same period last year and growth
Sales LY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY Change = [Total Sales] - [Sales LY]
Sales YoY % =
DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
In a visual by month, March 2026 shows March 2025 in Sales LY. DATEADD shifts by any interval and is more flexible:
Sales Previous Month =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )
Sales MoM % =
DIVIDE ( [Total Sales] - [Sales Previous Month], [Sales Previous Month] )
Sales Same Quarter LY =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, YEAR ) )
Intervals can be DAY, MONTH, QUARTER or YEAR. The first period has no previous period, so DIVIDE returns blank rather than an error. Format growth measures as percentages and show a minus sign clearly. Related functions: PARALLELPERIOD, PREVIOUSMONTH, PREVIOUSYEAR, DATESBETWEEN, DATESMTD, ENDOFMONTH, FIRSTDATE and LASTDATE.
Rolling totals and moving averages
The last 3 months, ending at the latest date in context:
Sales Last 3 Months =
CALCULATE (
[Total Sales],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)
Sales 3M Avg per Month = DIVIDE ( [Sales Last 3 Months], 3 )
For a 12-month rolling total, use -12, MONTH. A rolling window at the very start of the data covers fewer months, so mark early periods or hide them.
Running total (cumulative)
Cumulative Sales =
CALCULATE (
[Total Sales],
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
This removes the date filter and then keeps every date up to the latest one visible, a running total from the start of all data.
Avoid showing future periods
A YTD measure for a month that has not happened yet repeats the last value, which draws a flat line into the future. Limit it to dates with data:
Sales YTD (to last sale) =
IF (
MAX ( 'Date'[Date] ) <= MAX ( Sales[OrderDate] ),
TOTALYTD ( [Total Sales], 'Date'[Date] )
)
Or add an IsFuture flag column to the Date table and filter on it.
Why it returns blank or wrong values
| Symptom | Likely cause and fix |
|---|---|
| Every period shows the same value | the visual uses the fact table's date column, not the Date table's |
Sales LY is blank for the first year |
there is no earlier data: expected |
| All time-intelligence measures blank | Date table missing, not marked, or not related |
| Errors about duplicate dates | the Date table has repeated dates; keep one row per day |
| YTD resets at the wrong month | fiscal year-end date not given |
| Months appear in alphabetical order | sort the Month column by Month Number |
| Numbers differ between YTD and sum of months | the date column has time parts, or some rows are outside the Date table range |
| Auto date/time tables interfere | switch Auto date/time off in Options and use your own Date table |
If the fact table has date and time (a timestamp), create a separate date-only column in Power Query (DateTime.Date) to relate to the Date table, or the relationship will not match.
Choosing a design: calculation groups
If you have many base measures and want each in YTD, LY and so on, copying every pair of measures gets long. Calculation groups (created with Tabular Editor or in newer Power BI versions) define time-intelligence items once and apply them to every measure. That is an advanced topic; first be fluent with the explicit measures above.
Common mistakes
- No continuous Date table, or using the fact table's date column on the axis.
- Forgetting to mark the date table or create the relationship.
- Ignoring the fiscal year, so YTD starts in January when the company year starts in April.
- Comparing incomplete periods (this month so far against the whole of last month).
- Not handling the first period, which has no previous period.
Try it yourself
- Build a Date table, mark it, relate it to Sales, and sort Month by Month Number.
- Create
Sales YTD,Sales LYandSales YoY %, and chart them by month in 2025 and 2026. - Create a 3-month rolling total with
DATESINPERIOD. - Make a fiscal YTD for a year ending 31 March.
- Limit YTD so it does not draw into future months.
Check yourself
What does a Date table need for time intelligence to work?
One row per day with no gaps, unique dates, covering all the data, marked as a date table (or related correctly), and a relationship to the fact table's date column.
What does SAMEPERIODLASTYEAR do?
It returns the same dates one year earlier than the dates in the current filter context, so CALCULATE can evaluate the measure for the previous year.
How do you calculate year-over-year growth percentage?
DIVIDE ( [Sales] - [Sales LY], [Sales LY] ).
Why must you use the Date table's columns in visuals?
Filters on the fact table's date column do not move through the calendar that time-intelligence functions rely on, so results are wrong or blank.
Interview questions
How would you calculate sales for the same period last year?
With a proper Date table, CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), or DATEADD ( 'Date'[Date], -1, YEAR ).
Your YTD measure is blank. What do you check?
That a continuous Date table exists, is marked and is related to the fact table, that the visual uses the Date table's columns, and that the measure uses the Date table's date column.
How do you handle a financial year that starts in April?
Pass the year-end date to TOTALYTD, such as "3/31", and add fiscal year and fiscal quarter columns to the Date table.
Summary
- Time intelligence needs a continuous, marked Date table related to the fact table.
TOTALYTD,SAMEPERIODLASTYEAR,DATEADDandDATESINPERIODcover most reports.- Handle fiscal years, first periods and future dates explicitly.
- Most failures are caused by a missing date table or using the wrong date column.
Going deeper
- Time intelligence functions (DAX): the full list.
- TOTALYTD function: including the fiscal year-end argument.
- SAMEPERIODLASTYEAR function: reference.
- Set and use date tables in Power BI Desktop: requirements and how to mark one.
See how much of this you can explain
A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.