🎯 Situation

Un client avait construit un modèle Power BI sur six mois. Données de ventes, produits, clients — tout correctement connecté. Puis ils ont essayé d'ajouter une mesure YTD. TOTALYTD retournait de mauvais résultats. SAMEPERIODLASTYEAR affichait des blancs. Le problème n'était pas le DAX. C'était la table de dates manquante.

👉 Les fonctions de time intelligence de Power BI reposent sur une hypothèse : il existe une table de dates dédiée dans le modèle, marquée comme table de dates, connectée à la table de faits via une colonne date. Sans ça, TOTALYTD, SAMEPERIODLASTYEAR et DATEADD retournent de mauvais résultats ou échouent silencieusement.

⚠️ Challenge

📅 What a proper date table must have

  • One row per day — no gaps, covering the full range of your data plus future periods
  • A DateKey column (integer YYYYMMDD or the date itself) as the primary key
  • Calendar columns: Year, Quarter, Month number, Month name, Week number
  • Fiscal calendar columns if your fiscal year differs from calendar year
  • IsWorkingDay flag — essential for business-day calculations

❌ What breaks without a date table

  • TOTALYTD, TOTALQTD, TOTALMTD return wrong or blank values
  • SAMEPERIODLASTYEAR produces blanks or incorrect period comparisons
  • DATEADD shifts by wrong intervals
  • Time intelligence across multiple fact tables fails completely
  • Slicers and filters on date don't propagate correctly to time calculations

🔍 Analyse

The fastest way to create a correct date table in Power BI is DAX using CALENDARAUTO() or a manual range:

DimDate = 
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2030, 12, 31)
RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "Year",           YEAR([Date]),
    "Month Number",   MONTH([Date]),
    "Month Name",     FORMAT([Date], "MMMM"),
    "Quarter",        "Q" & QUARTER([Date]),
    "Week Number",    WEEKNUM([Date]),
    "Day of Week",    FORMAT([Date], "dddd"),
    "Is Working Day", IF(WEEKDAY([Date], 2) <= 5, TRUE, FALSE),
    "Year-Month",     FORMAT([Date], "YYYY-MM")
)

After creating the table: 1. Mark it as a Date Table (right-click → Mark as date table → select the Date column) 2. Create a relationship from DimDate[Date] to your fact table's date column 3. Set the relationship to single direction (from DimDate to fact)

That's it. TOTALYTD and all time intelligence functions will now work correctly.

✓️ Bonne pratique

Three rules for date tables:

1. One date table per model — not one per fact table. If you have sales and purchases both with dates, both connect to the same DimDate. 2. Always mark it as a Date Table — this step is mandatory, not optional. Power BI uses it to validate time intelligence functions. 3. Cover more dates than you need — start 2 years before your earliest data, end 3 years after today. You'll never rebuild the date table because a new fiscal year wasn't included.

💡 Synthèse

The date table is the least glamorous part of a Power BI model and the most critical. Build it first, before any measures. Mark it correctly. Connect it to every fact table with a date column. Then every time intelligence calculation works — consistently, correctly, and without debugging.

👉 YTD, month-over-month, rolling 12 months — none of them work correctly without a proper date table.

Build it first. Mark it. Connect it. Then write the measures.