Power BI DAX Time Intelligence
What Is Time Intelligence?
Time intelligence functions calculate over periods of time: the year to date, the same period last year, the previous month, and so on.
They answer two of the most common questions in business reports: "How much have we sold so far this year?" and "Are we doing better than last year?"
This page uses the Poke Mart model described in DAX Introduction, with the Total Sales measure from DAX Measures.
You Need a Date Table
Time intelligence functions need a proper date table.
You built one in the Date Table chapter:
- It was made with
CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)), so it has one row for every day, with no gaps. - It covers whole years, from January 1 to December 31.
- It is marked as a date table.
- It has a relationship to the Sales table, from Sales[Date] to 'Date'[Date].
In the functions on this page, always use the date column of the Date table, 'Date'[Date].
The functions move and extend dates within this column, so it must contain every date.
In visuals, use the Year, Quarter and Month columns of the Date table.
Note: If your date table is not marked as a date table, or has missing dates, the functions on this page can return wrong results or errors.
Year to Date with TOTALYTD
A year-to-date (YTD) total adds up everything from January 1 up to the current date, month or quarter.
It starts again every new year.
Syntax
TOTALYTD(expression, dates [, filter] [, year_end_date])
| Argument | Meaning |
|---|---|
| expression | What to add up, usually a measure. |
| dates | The date column of the Date table, 'Date'[Date]. |
| filter | Optional. An extra filter, like in CALCULATE. |
| year_end_date | Optional. The last day of a fiscal year, as text, for example "6/30". The default is December 31. |
Example
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Make a matrix with Month from the Date table in Rows, and Total Sales and Sales YTD in Values.
Add a slicer with Year.
A slicer on a number column like Year starts as a Between slider, so go to Format visual > Visual > Slicer settings > Options and set Style to Vertical list. Then select 2025:
| Month (2025) | Total Sales | Sales YTD |
|---|---|---|
| Jan | 317,300 | 317,300 |
| Feb | 289,900 | 607,200 |
| Mar | 370,750 | 977,950 |
| Apr | 359,450 | 1,337,400 |
| May | 414,500 | 1,751,900 |
| Jun | 423,850 | 2,175,750 |
| Jul | 510,200 | 2,685,950 |
| Aug | 585,600 | 3,271,550 |
| Sep | 356,300 | 3,627,850 |
| Oct | 363,250 | 3,991,100 |
| Nov | 398,500 | 4,389,600 |
| Dec | 634,000 | 5,023,600 |
| Total | 5,023,600 | 5,023,600 |
Each month adds its sales to the months before it.
At the end of March, the YTD is 977,950, the same as the sales of the first quarter.
In December it reaches 5,023,600, the total of the year.
Select 2024 in the slicer, and the running total starts again in January 2024: 290,600 in January, and 4,393,200 in December.
TOTALYTD is a short way to write CALCULATE([Total Sales], DATESYTD('Date'[Date])).
Both give the same result.
Last Year with SAMEPERIODLASTYEAR
SAMEPERIODLASTYEAR takes the dates of the current filter context and moves them back one year.
Used in CALCULATE, it calculates a measure for the same period last year.
Syntax
SAMEPERIODLASTYEAR(dates)
Example
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
LY is short for "last year".
Clear the Year slicer, and make a matrix with Year in Rows:
| Year | Total Sales | Sales LY |
|---|---|---|
| 2024 | 4,393,200 | |
| 2025 | 5,023,600 | 4,393,200 |
| Total | 9,416,800 | 4,393,200 |
In the 2025 row, the filter context holds all the dates of 2025. SAMEPERIODLASTYEAR moves them back to 2024, so Sales LY shows the sales of 2024: 4,393,200.
Why 2024 Is Blank
In the 2024 row, SAMEPERIODLASTYEAR moves the dates back to 2023. But the Poke Mart data starts on January 1, 2024, and the Date table has no dates in 2023. There are no sales to add up, so the result is blank.
Blank is not the same as zero.
It means "no data", and Power BI shows an empty cell.
The Total row covers both years.
Moved back one year, they become 2023 and 2024, and only 2024 has data.
So the Total row shows 4,393,200 again.
Totals like this mean little, and you can turn them off in the format settings of the visual (see Format Visuals).
Year-over-Year Growth
With Sales LY, the growth measure is short.
YoY is short for "year over year":
Example
Sales YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Format it as Percentage with 2 decimal places, and add it to the matrix by Year:
| Year | Total Sales | Sales LY | Sales YoY % |
|---|---|---|---|
| 2024 | 4,393,200 | ||
| 2025 | 5,023,600 | 4,393,200 | 14.35% |
Sales grew by 630,400 from 2024 to 2025, which is 14.35%.
This is the same result as Sales YoY % VAR on the Variables page.
In 2024, Sales LY is blank.
DIVIDE returns a blank when it divides by a blank or by zero, so Sales YoY % is blank too, instead of an error.
(The Total row, if you show it, gives 114.35%: it compares both years with 2024 alone, which is another reason to turn it off.)
By Quarter
Put Year and Quarter from the Date table in the Rows of the matrix, with Year above Quarter.
Quarter lands in Columns when you tick it, so drag it into Rows.
Then select Expand all down one level in the hierarchy (the forked arrow at the top of the matrix) so all eight quarters show:
| Year | Quarter | Total Sales | Sales LY | Sales YoY % |
|---|---|---|---|---|
| 2024 | Q1 | 852,950 | ||
| 2024 | Q2 | 1,119,400 | ||
| 2024 | Q3 | 1,223,600 | ||
| 2024 | Q4 | 1,197,250 | ||
| 2025 | Q1 | 977,950 | 852,950 | 14.66% |
| 2025 | Q2 | 1,197,800 | 1,119,400 | 7.00% |
| 2025 | Q3 | 1,452,100 | 1,223,600 | 18.67% |
| 2025 | Q4 | 1,395,750 | 1,197,250 | 16.58% |
Each quarter of 2025 is compared with the same quarter of 2024. Every quarter grew: the most in Q3 (18.67%), and the least in Q2 (7.00%).
All four quarters of 2024 are blank, for the same reason as before: there is no 2023 to compare with.
The year subtotals of the matrix show the same values as the matrix by Year.
DATEADD
SAMEPERIODLASTYEAR always goes back exactly one year.
DATEADD can move the dates by any number of years, quarters, months or days.
A negative number moves back in time, a positive number moves forward.
Syntax
DATEADD(dates, number_of_intervals, interval)
| Argument | Meaning |
|---|---|
| dates | The date column of the Date table. |
| number_of_intervals | How many intervals to move. Negative numbers move back in time. |
| interval | One of YEAR, QUARTER, MONTH or DAY, written without quotes. |
Example
Sales Last Month = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))
In the matrix by Month, with 2025 selected in the Year slicer (the Total row is not shown):
| Month (2025) | Total Sales | Sales Last Month |
|---|---|---|
| Jan | 317,300 | 516,550 |
| Feb | 289,900 | 317,300 |
| Mar | 370,750 | 289,900 |
| Apr | 359,450 | 370,750 |
| May | 414,500 | 359,450 |
| Jun | 423,850 | 414,500 |
| Jul | 510,200 | 423,850 |
| Aug | 585,600 | 510,200 |
| Sep | 356,300 | 585,600 |
| Oct | 363,250 | 356,300 |
| Nov | 398,500 | 363,250 |
| Dec | 634,000 | 398,500 |
Look at January: Sales Last Month is 516,550, the sales of December 2024. The slicer says 2025, but DATEADD moves the dates of January 2025 back into December 2024.
SAMEPERIODLASTYEAR returns the same dates as DATEADD('Date'[Date], -1, YEAR).
So Sales LY could also be written with DATEADD.
PREVIOUSMONTH
PREVIOUSMONTH returns all the dates of the month before the first date in the current filter context:
CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))
In a matrix by Month, it gives the same results as Sales Last Month.
The difference shows at the quarter level.
For 2025 Q2, PREVIOUSMONTH returns one month, March 2025 (370,750).
DATEADD moves the whole quarter back one month, to March through May 2025 (1,144,700).
There are similar functions for other periods, like PREVIOUSQUARTER, PREVIOUSYEAR and NEXTMONTH.
Time Intelligence Functions on This Page
| Function | Returns | Used in |
|---|---|---|
| TOTALYTD | The year-to-date value of an expression | Sales YTD |
| DATESYTD | The dates from January 1 to the last date in the filter context | The long form of Sales YTD |
| SAMEPERIODLASTYEAR | The dates in the filter context, moved back one year | Sales LY |
| DATEADD | The dates in the filter context, moved by a number of intervals | Sales Last Month |
| PREVIOUSMONTH | All the dates of the previous month | A month-level alternative to DATEADD |
What You Learned
- Time intelligence functions need a complete Date table that is marked as a date table.
TOTALYTD([Total Sales], 'Date'[Date])gives a running total that starts again every year.SAMEPERIODLASTYEARin CALCULATE gives last year's sales. It is blank for 2024, because there is no data for 2023.DIVIDE([Total Sales] - [Sales LY], [Sales LY])gives the year-over-year growth: 14.35% for 2025.- DATEADD moves dates by any number of years, quarters, months or days.
Good job! You have finished the DAX chapter.
In the next chapter, you will show these measures in visuals.