🎯 Situation

A client had built a Power BI model over six months. Sales data, product data, customer data — all connected correctly. Then they tried to add a YTD measure. TOTALYTD returned wrong results. SAMEPERIODLASTYEAR showed blanks. DATEADD produced numbers that made no sense. They spent two days debugging DAX. The problem wasn't the DAX. It was the missing date table.

👉 Power BI's time intelligence functions are built on one assumption: there is a dedicated date table in the model, marked as a date table, connected to your fact table via a date column. Without that, TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, and all related functions either return wrong results or fail silently.

⚠️ 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

🔍 Analysis

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.

✓️ Best Practice

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.

💡 Summary

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.