Date table in DAX

Start from first principles. Ask yourself what SAMEPERIODLASTYEAR physically has to do. The report cell you're in carries a filter context, say "March 2024." The function's job is to return a different set of dates, "March 2023." To do that it has to know what March 2023 even is. It needs to know which calendar days that month contains, that they form one contiguous block, and that they sit one year before the current filter. A loose Sales[OrderDate] column buried in your fact table can't tell it any of that, because it only holds the days on which sales happened. If you sold nothing on 7 March, that day is simply missing. A function trying to "shift the whole period back one year" now has holes in both the source and the target period. So time intelligence needs a spine of dates that is complete and unbroken, with one row for every calendar day in the range whether or not anything happened on it.

What does a date table need?

That spine is the Date table (often called Date or Calendar). Its three non-negotiable properties:

1. One row per day, contiguous. From the first date in your data to the last, every day is present with no gaps. That's 365 or 366 rows per year, always. TOTALYTD, DATEADD, and the whole family will misbehave if a day is missing, and they won't throw an error to warn you.
2. A real date-typed key column. You need an actual Date column. A text field won't do, and neither will a Year integer plus a Month integer. This is the most common beginner error of all. People wire time intelligence to a [Year] and [Month] pair and then wonder why nothing shifts. The functions read a date, and only a date.
3. Marked as a Date Table. You must explicitly tell the engine "this table is the calendar, and this is its date key." Until you do, DAX treats it as an ordinary table and the built-in time functions can't use it.

How do you create a date table in Power Pivot?

There are three UI paths for building it in M365 Power Pivot.

• Generate one in Power Pivot. Open Power Pivot ▸ Manage, then in the Power Pivot window go Design ▸ Date Table ▸ New. Excel creates a Calendar table spanning the min-to-max dates found in your model. It's already contiguous and already marked, which makes this the fastest correct start.
• Load your own. Build a calendar in Power Query, starting from a List.Dates seed and adding Year, Quarter, Month, MonthNo, FiscalYear, WeekdayName columns. Load it to the model, then mark it. Select the table's tab, go to Design ▸ Mark as Date Table ▸ Mark as Date Table…, and choose the date column. This is what professionals ship, because you control the fiscal columns and sort orders.
• Relate it. In Power Pivot ▸ Manage ▸ Diagram View, drag Date[Date] onto Sales[OrderDate] to build the one-to-many relationship. Now a filter on the Date table flows into the fact table, and every measure inherits the calendar.

What happens if you skip marking the table?

Once the table is marked and related, every function that follows "just works," because they all run off the marked date column behind the scenes. Skip the marking and you'll spend an afternoon debugging blanks. Building the table can feel like busywork the first time. It's really the coordinate system everything else here stands on.