Building a calendar table

Why you need one

Time intelligence will not work without it

DAX time intelligence functions — SAMEPERIODLASTYEAR, TOTALYTD, DATEADD — require a table with one row per date, contiguous, with no gaps, covering the full range of your data.

A date column on the fact table is not enough, because dates with no transactions are missing, and the functions need a complete timeline.

Building one in DAX

Calendar =
VAR MinDate = MIN('Sales'[OrderDate])
VAR MaxDate = MAX('Sales'[OrderDate])
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(YEAR(MinDate),1,1), DATE(YEAR(MaxDate),12,31)),
    "Year",        YEAR([Date]),
    "Month",       FORMAT([Date], "mmm"),
    "MonthNo",     MONTH([Date]),
    "Quarter",     "Q" & QUARTER([Date]),
    "YearMonth",   FORMAT([Date], "yyyy-mm"),
    "WeekdayName", FORMAT([Date], "ddd"),
    "IsWeekend",   WEEKDAY([Date],2) > 5
)

Starting at 1 January and ending at 31 December matters — a part-year table breaks year-over-year comparisons at the edges.

Sorting month names

Month names sort alphabetically

Apr, Aug, Dec, Feb… is what you get by default, because they are text.

Select the Month column → Column tools → Sort by column → MonthNo. Do the same for weekday names. It is the commonest Power BI annoyance and a two-click fix.

Mark as date table

Right-click the table → Mark as date table, choosing the date column.

This tells Power BI it is the authoritative calendar, and time intelligence then behaves correctly. Without it you can get subtly wrong results at period boundaries — which is worse than an error, because nothing tells you.

Turn off auto date/time

By default Power BI creates a hidden date hierarchy for every date column in the model. On a large model that is a great deal of hidden storage for tables you will never use.

File → Options → Data Load → uncheck Auto date/time. Then build one proper calendar table and relate the dates to it.