← Back to Chapters

Time Intelligence in Power BI

? Time Intelligence in Power BI

? Quick Overview

Time Intelligence in Power BI allows you to analyze data over time such as MTD, QTD, YTD, rolling periods, and year comparisons.

? Key Concepts

  • Time intelligence works only on measures
  • A dedicated Calendar table is mandatory
  • Relationships drive time-based calculations
  • Filter context controls the results

? Syntax / Theory

? Generic Time Intelligence Pattern 
// Base pattern for all time intelligence measures
Measure =
FUNCTION(
[Base Measure],
Calendar[Date]
)

? Use Cases

  • Month-over-month performance analysis
  • Quarterly and yearly sales tracking
  • Rolling 7-day or 30-day trends
  • Financial and management reporting

? Interactive DAX Formula Builder

Select a logic type to generate the DAX measure syntax:

MTD Sales = TOTALMTD( [Total Sales], 'Calendar'[Date] )

⚡ Time Intelligence Logic Simulator

$0
$0
$0
$0

✅ Best Practices

  • Always create a separate Calendar table
  • Mark the Calendar table as Date Table
  • Use measures instead of calculated columns
  • Ensure the Calendar table has continuous dates
  • Use SAMEPERIODLASTYEAR only with complete date ranges
  • Keep one active relationship with the date column

? Try It Yourself

  • Create MTD, QTD, and YTD measures
  • Add a Date slicer using Calendar table
  • Change dates and observe calculations
  • Create a rolling 30-day sales measure