🎯 Situación

Un cliente había construido un modelo de Power BI durante seis meses. Datos de ventas, productos, clientes — todo conectado correctamente. Luego intentaron agregar una medida YTD. TOTALYTD devolvía resultados incorrectos. SAMEPERIODLASTYEAR mostraba blancos. El problema no era el DAX. Era la tabla de fechas faltante.

👉 Las funciones de time intelligence de Power BI están construidas sobre un supuesto: existe una tabla de fechas dedicada en el modelo, marcada como tabla de fechas, conectada a la tabla de hechos a través de una columna de fecha.

⚠️ El reto

📅 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

🔍 Análisis

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.

✓️ Buena práctica

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.

💡 Síntesis

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.