Concept
1. Requirements (don't skip)
- A Calendar table with every date, no gaps, covering all years in your data (Lesson 1).
- It's marked as Date Table.
fact_Sales[OrderDate]→Calendar[Date]relationship.- Pivots use Calendar fields (FY, Month), never the fact table's date.
Break any of these and time functions return blanks or wrong numbers.
2. Year-to-date (Indian FY)
Revenue YTD := TOTALYTD([Total Revenue], 'Calendar'[Date], "3/31")
The third argument is the year-end date: "3/31" = 31 March, so the year restarts every April. Without it, YTD restarts in January.
Pivot: Rows = FY, Month (Calendar). At September 2025, Revenue YTD = 12,68,13,258 (April–September). At March 2026 it reaches the full-year 29,49,96,410.
Quarter-to-date: TOTALQTD([Total Revenue], 'Calendar'[Date]) (calendar quarters).
3. Month-to-date
Revenue MTD := TOTALMTD([Total Revenue], 'Calendar'[Date])
Useful with Date in Rows (day level) or a daily dashboard: "sales so far this month".
4. Same period last year
Revenue LY := CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY Growth := [Total Revenue] - [Revenue LY]
YoY % := DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY])
SAMEPERIODLASTYEAR shifts the cell's dates back one year — the same month, quarter or FY.
| Cell | Revenue | Revenue LY | YoY % |
|---|---|---|---|
| Oct 2025 | 3,60,09,313 | 3,05,31,900 | +17.9% |
| FY2025-26 | 29,49,96,410 | 26,56,58,820 | +11.0% |
5. YTD vs last year's YTD
Revenue YTD LY := CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR('Calendar'[Date]))
YTD Growth % := DIVIDE([Revenue YTD] - [Revenue YTD LY], [Revenue YTD LY])
At September 2025: YTD 12,68,13,258 vs last year's YTD 11,60,23,245 → +9.3%. This is the number management usually asks for in mid-year reviews.
6. Previous month (for MoM)
Revenue PM := CALCULATE([Total Revenue], DATEADD('Calendar'[Date], -1, MONTH))
MoM % := DIVIDE([Total Revenue] - [Revenue PM], [Revenue PM])
DATEADD shifts by any number of days, months, quarters or years. (KPI meaning of MoM/YoY in Module 7.)
7. Watch out
- Partial periods: if data ends on 15 October, "Oct YoY" compares half a month with a full month. Show the last complete month, or compare MTD with MTD LY.
- Calendar too long: a Calendar running to December 2026 makes YTD show flat future months — filter the pivot to dates with data, or end the Calendar at the last data date.
- First year: LY measures are blank for FY2024-25 (no earlier data) — that's correct, not a bug.
8. The core measure set (copy into every model)
Total Revenue, Total Cost, Profit, Margin %, Orders, Avg Order Value, Revenue YTD, Revenue LY, YoY %, Revenue YTD LY, YTD Growth %, Revenue PM, MoM %. With these, most monthly business reviews are a pivot and two slicers.
Common mistakes
Missing "3/31" (YTD resets in January). Using the fact table's date in pivots. Calendar not marked or with gaps. Comparing a partial current month with a full last-year month.