Time Intelligence

What You'll Learn

• Time intelligence functions
• YTD, MTD, QTD calculations
• Compare to previous periods
• Year-over-year growth

Time Intelligence Basics

Time intelligence = calculations based on dates.

Common needs:

• Year to Date (YTD)
• Month to Date (MTD)
• Last Year Same Period
• Growth rates

Requirement: Must have Date table with continuous dates!

Key Functions

TOTALYTD

Year to Date total: YTD Sales = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Shows cumulative total from Jan 1 to selected date.

TOTALMTD

Month to Date: MTD Sales = TOTALMTD(SUM(Sales[Amount]), Calendar[Date])

TOTALQTD

Quarter to Date: QTD Sales = TOTALQTD(SUM(Sales[Amount]), Calendar[Date])

Previous Period Comparisons

SAMEPERIODLASTYEAR

Last year same period: LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))

Example: March 2024 shows March 2023

DATEADD

Flexible period shift: Last Month = CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH))

Parameters:

• -1, MONTH (previous month)
• -1, YEAR (previous year)
• -7, DAY (last week)

PARALLELPERIOD

Entire previous period: Previous Year = CALCULATE([Total Sales], PARALLELPERIOD(Calendar[Date], -1, YEAR))

Growth Calculations

Year-over-Year Growth

YoY Growth = VAR CurrentYear = [Total Sales] VAR LastYear = [LY Sales] RETURN DIVIDE(CurrentYear - LastYear, LastYear)

Format as percentage!

Month-over-Month

MoM Growth = VAR ThisMonth = [Total Sales] VAR LastMonth = CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH)) RETURN DIVIDE(ThisMonth - LastMonth, LastMonth)

Moving Averages

3-Month Moving Average

3M Avg = CALCULATE( AVERAGE(Sales[Amount]), DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH) )

Smooths out fluctuations!

Date Table Requirements

Must have:

• Continuous dates (no gaps!)
• Marked as Date table
• Cover all transaction dates

How to create: Modeling > New Table Calendar = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))

Mark as Date table: Right-click > Mark as date table

Try This Exercise

Create these measures:

1. YTD Sales = TOTALYTD([Total Sales], Calendar[Date])
2. Last Year Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
3. YoY Growth % = DIVIDE([Total Sales] - [Last Year Sales], [Last Year Sales])
4. Add to cards and test with date slicer

Next Steps

Learn about Filter Context and how CALCULATE really works!

Tip: Time intelligence unlocks powerful analysis. Master these patterns!