⏰ DAX Time Intelligence Functions

Master Running Totals & Time-Based Analysis

🎓 Ghaziabad Mohan Nagar

👨‍💼 Designed by: HR Prateek Sharma

Understanding Time Intelligence

Time Intelligence Functions allow you to perform time-based calculations like running totals, cumulative sales, and period comparisons.

MTD (Month To Date), YTD (Year To Date), and QTD (Quarter To Date) provide cumulative values from the start of a period to the current date.

📊 MTD

Month To Date - Running total from the beginning of the month to the current date. Perfect for tracking monthly cumulative sales.

📈 YTD

Year To Date - Running total from the beginning of the year to the current date. Essential for annual performance tracking.

📉 QTD

Quarter To Date - Running total from the beginning of the quarter to the current date. Great for quarterly analysis.

DAX Time Intelligence Formulas

📅 Running Totals (Cumulative)

total MTD sales = TOTALMTD(SUM(Orders[Sales]), 'date dim'[Date])

// Month to Date sales - cumulative from start of month

total YTD sales = TOTALYTD(SUM(Orders[Sales]), 'date dim'[Date])

// Year to Date sales - cumulative from start of year

total QTD sales = TOTALQTD(SUM(Orders[Sales]), 'date dim'[Date])

// Quarter to Date sales - cumulative from start of quarter

🎯 CALCULATE with Filters

west sales = CALCULATE(SUM(Orders[Sales]), Orders[Region] = "west")

// Aggregates sales data filtered by West region

⏮️ Previous Period Comparisons

previous year sales = CALCULATE(SUM(Orders[Sales]), PREVIOUSYEAR('date dim'[Date]))

// Sales from the previous year

previous month sales = CALCULATE(SUM(Orders[Sales]), PREVIOUSMONTH('date dim'[Date]))

// Sales from the previous month

previous quarter sales = CALCULATE(SUM(Orders[Sales]), PREVIOUSQUARTER('date dim'[Date]))

// Sales from the previous quarter

⏭️ Next Period Projections

next year sales = CALCULATE(SUM(Orders[Sales]), NEXTYEAR('date dim'[Date]))

// Sales for the next year

next month sales = CALCULATE(SUM(Orders[Sales]), NEXTMONTH('date dim'[Date]))

// Sales for the next month

🔄 Same Period Last Year (SPLY)

SPLY sale =

-- SPLY >> Same Period Last Year

CALCULATE(SUM(Orders[Sales]), SAMEPERIODLASTYEAR('date dim'[Date]))

// Compares current period with the same period from last year

🔑 CALCULATE Function - The Power Tool

CALCULATE is one of the most powerful DAX functions. It allows us to aggregate data based on specific filters.

Use CALCULATE to:

✨ Filter data by region, product, category, or any dimension

✨ Combine with time intelligence functions for period comparisons

✨ Create dynamic measures that respond to user selections

✨ Build complex business logic with multiple filter conditions

📊 Running Totals

Use TOTALMTD, TOTALYTD, TOTALQTD for cumulative calculations that show progressive totals over time periods.

🔍 Period Analysis

Compare current performance with PREVIOUSYEAR, PREVIOUSMONTH, PREVIOUSQUARTER functions for trend analysis.

📈 Year-over-Year

SAMEPERIODLASTYEAR is perfect for comparing the same time period across different years for accurate YoY analysis.

⚡ Dynamic Filtering

CALCULATE enables context-sensitive calculations that automatically adapt to slicers and filters in your reports.