Tutorials › Power BI › Time Intelligence in DAX

Time Intelligence in DAX

Calculate YTD, MTD, same period last year, and MoM growth using DAX time functions

Time intelligence is what makes Power BI truly powerful for business reporting. Questions like "How does this month compare to the same month last year?" or "What is our year-to-date revenue?" require time intelligence DAX functions. Prerequisite: time intelligence functions require a proper Date dimension table with continuous dates (no gaps) marked as a Date Table. If you have not set this up yet, see the "Date Table" tutorial first. The most used time intelligence functions in business reports: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD, PREVIOUSMONTH, and PREVIOUSYEAR.
Time Intelligence: Current Year vs Last Year
Same measure evaluated in two different time contexts using SAMEPERIODLASTYEAR
Jan
Feb
Mar
Apr
May
Jun
Current Year
Sales CY = SUM(Sales[Amount])
Responds to current date filters
Last Year
Sales LY = CALCULATE(SUM(...), SAMEPERIODLASTYEAR(Date[Date]))
Shifts context back 12 months
YoY Growth % = DIVIDE([Sales CY] - [Sales LY], [Sales LY])

Example

Most common time intelligence measures
YEAR-TO-DATE:
Sales YTD = TOTALYTD(SUM(Sales[Amount]), Date[Date])

SAME PERIOD LAST YEAR:
Sales LY =
  CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Date[Date])
  )

YEAR-OVER-YEAR GROWTH %:
YoY Growth % =
  DIVIDE(
    SUM(Sales[Amount]) - [Sales LY],
    [Sales LY]
  )

PREVIOUS MONTH:
Sales Prev Month =
  CALCULATE(
    SUM(Sales[Amount]),
    PREVIOUSMONTH(Date[Date])
  )

MONTH-OVER-MONTH CHANGE:
MoM Change = SUM(Sales[Amount]) - [Sales Prev Month]

ROLLING 3 MONTHS:
Sales 3M Rolling =
  CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -3, MONTH)
  )
💡 All time intelligence functions require a continuous Date table marked as a Date Table in Power BI.

Key Points

  • ✓Time intelligence ONLY works with a proper Date table — no gaps, marked as "Date Table"
  • ✓TOTALYTD resets at the start of each year automatically
  • ✓SAMEPERIODLASTYEAR shifts the entire filter context back exactly one year
  • ✓Use DIVIDE() for growth % — it handles blank/zero denominators gracefully
  • ✓DATEADD(Date[Date], -1, YEAR) is equivalent to SAMEPERIODLASTYEAR for year shift

Practice Question

Which DAX function calculates the same metric for the same period in the previous year?