Time Intelligence, Rolling Metrics and Advanced KPI Analysis

Instructor: Alaa Hady · Language: English or Arabic · Suggested duration: 6 hours · Level: Intermediate to Advanced (Level 200–300)

Objectives

• Build bulletproof period-over-period, rolling, and cumulative performance metrics.
• Handle non-standard fiscal calendars, 4-4-5 retail calendars, and working-day adjustments.
• Prevent misleading comparisons caused by incomplete current periods or missing future dates.
• Standardize KPI variance, pace-to-target, and run-rate calculations across the organization.

Outline and contents

1. Enterprise Date Table architecture: offset columns (Month Offset, Year Offset, Working Day flags)
2. Standard DAX Time Intelligence vs. Offset-based Time Intelligence: when built-in functions fail
3. Preventing the "Incomplete Period" trap: capping YTD and PY comparisons to the last date with actuals
4. Rolling averages, Moving Annual Totals (MAT / LTM), and trailing N-period patterns
5. Fiscal calendars, 52/53-week (4-4-5) retail calendars, and custom week-over-week comparisons
6. Working days / billing days normalization and pace-to-target run-rate projections
7. Price-Volume-Mix (PVM) variance decomposition across time periods
8. Dynamic Time Horizon switching using Calculation Groups and Field Parameters
9. Validation and reconciliation lab against Finance period-close numbers

Target audience

Finance analysts, FP&A teams, revenue analysts, performance analysts, and experienced Power BI developers.

Expected outcomes

Participants can build time-based calculations that survive edge cases (leap years, partial months, fiscal shifts, retail weeks) and align 100% with corporate accounting rules.