Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX Time Intelligence


Share

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])
ArgumentMeaning
expressionWhat to add up, usually a measure.
datesThe date column of the Date table, 'Date'[Date].
filterOptional. An extra filter, like in CALCULATE.
year_end_dateOptional. 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 SalesSales YTD
Jan317,300317,300
Feb289,900607,200
Mar370,750977,950
Apr359,4501,337,400
May414,5001,751,900
Jun423,8502,175,750
Jul510,2002,685,950
Aug585,6003,271,550
Sep356,3003,627,850
Oct363,2503,991,100
Nov398,5004,389,600
Dec634,0005,023,600
Total5,023,6005,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:

YearTotal SalesSales LY
20244,393,200
20255,023,6004,393,200
Total9,416,8004,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:

YearTotal SalesSales LYSales YoY %
20244,393,200
20255,023,6004,393,20014.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:

YearQuarterTotal SalesSales LYSales YoY %
2024Q1852,950
2024Q21,119,400
2024Q31,223,600
2024Q41,197,250
2025Q1977,950852,95014.66%
2025Q21,197,8001,119,4007.00%
2025Q31,452,1001,223,60018.67%
2025Q41,395,7501,197,25016.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)
ArgumentMeaning
datesThe date column of the Date table.
number_of_intervalsHow many intervals to move. Negative numbers move back in time.
intervalOne 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 SalesSales Last Month
Jan317,300516,550
Feb289,900317,300
Mar370,750289,900
Apr359,450370,750
May414,500359,450
Jun423,850414,500
Jul510,200423,850
Aug585,600510,200
Sep356,300585,600
Oct363,250356,300
Nov398,500363,250
Dec634,000398,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

FunctionReturnsUsed in
TOTALYTDThe year-to-date value of an expressionSales YTD
DATESYTDThe dates from January 1 to the last date in the filter contextThe long form of Sales YTD
SAMEPERIODLASTYEARThe dates in the filter context, moved back one yearSales LY
DATEADDThe dates in the filter context, moved by a number of intervalsSales Last Month
PREVIOUSMONTHAll the dates of the previous monthA 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.
  • SAMEPERIODLASTYEAR in 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.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.