🎯 Situation

Après l'article sur la table de dates du mois dernier, la question de suivi la plus fréquente était : 'OK, j'ai la table de dates. Maintenant, comment j'écris vraiment le YTD, le mois sur mois, et le glissant 12 mois ?' Trois patterns. Quatre mesures au total. Les voici — avec le DAX exact, les erreurs fréquentes, et pourquoi chaque pattern fonctionne comme il fonctionne.

👉 La Time Intelligence DAX a un prérequis : une table de dates correcte, marquée comme table de dates, connectée à ta table de faits. Si tu ne l'as pas encore construite, cet article ne t'aidera pas — construis d'abord la table de dates (on l'a couvert en Semaine 33). Une fois en place, ces quatre mesures prennent 20 minutes à écrire.

⚠️ Challenge

📅 The 4 measures every BI report needs

  • Revenue YTD — cumulative revenue from January 1st of the current year to the selected date
  • Revenue MTD — cumulative revenue from the 1st of the current month to the selected date
  • Revenue vs. Same Period Last Year — the selected period vs. the same period 12 months ago
  • Rolling 12 Months Revenue — the last 365 days from the selected date, always moving

❌ What breaks time intelligence

  • No date table (or not marked as date table) → all functions return blank or wrong values
  • Gaps in the date table → YTD accumulations reset or skip periods
  • Multiple date columns in the fact table connected to the same date table → ambiguous relationships
  • Date column in the fact table is a datetime type instead of date → filter context doesn't work correctly

🔍 Analyse

The 4 measures in DAX:

-- 1. Year-To-Date
Revenue YTD =
TOTALYTD([Total Revenue], DimDate[Date])
-- 2. Month-To-Date
Revenue MTD =
TOTALMTD([Total Revenue], DimDate[Date])
-- 3. Same Period Last Year
Revenue SPLY =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DimDate[Date])
)
-- 4. Rolling 12 Months
Revenue R12M =
CALCULATE(
    [Total Revenue],
    DATESINPERIOD(
        DimDate[Date],
        LASTDATE(DimDate[Date]),
        -12,
        MONTH
    )
)
-- Bonus: Year-over-Year growth %
YoY Growth % =
VAR CurrentYear = [Revenue YTD]
VAR PriorYear = CALCULATE([Revenue YTD], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
IF(
    PriorYear <> 0,
    DIVIDE(CurrentYear - PriorYear, PriorYear),
    BLANK()
)

✓️ Bonne pratique

Three things to know about these measures:

1. TOTALYTD resets on January 1st — if your fiscal year doesn't start in January, use the fiscal year end parameter: TOTALYTD([Revenue], DimDate[Date], "06/30") for a June 30 fiscal year end.

2. SAMEPERIODLASTYEAR returns blank for dates with no comparison period — this is correct behavior, not a bug. A partial month in the current year will compare correctly to the same partial month last year.

3. Rolling 12 months is the most useful of the four for trend analysis — it smooths seasonality by always including a full year of data. Use it alongside MTD and YTD for a complete picture: what happened this month, this year, and over the trailing year.

💡 Synthèse

Four measures. Twenty minutes to write once the date table is in place. These four patterns cover the time-based questions in virtually every management report: how are we doing this year, this month, compared to last year, and over the last 12 months? Learn to write them from memory — they come up in every project.

👉 Time intelligence DAX isn't complicated. It just requires the right foundation.

Build the date table. Write these four measures. Every time comparison question answers itself.