Date Tables & Table Properties

Every model needs a date table

Creating a date table

There are three common approaches:

Option 1: DAX CALENDARAUTO (recommended)

Date = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "Quarter", "Q" & CEILING(MONTH([Date]) / 3, 1),
    "WeekDay", FORMAT([Date], "dddd"),
    "DayOfWeek", WEEKDAY([Date], 2)
)

CALENDARAUTO() scans all date columns in your model and creates a contiguous date range. Be aware that it may pick up irrelevant dates from other columns — if your model has a “ContractEndDate” of 2099, the table will extend to 2099. Use CALENDAR() with explicit dates when you need precise control.

Option 2: DAX CALENDAR (explicit range)

Date = 
CALENDAR(DATE(2020, 1, 1), DATE(2026, 12, 31))

Use when you want to control the exact date range rather than auto-detecting from data.

Option 3: Power Query (M code)

Create a date table in Power Query using List.Dates(). Useful when you need complex fiscal year logic or custom columns that are easier in M than DAX.

Marking the date table

After creating it, you must mark it:

1. Select the Date table in the model
2. Table tools → Mark as date table
3. Select the Date column

This tells Power BI to use your custom date table instead of its auto-generated one — and enables full time intelligence support.

💡 Exam tip: what makes a valid date table

The exam tests whether you know the requirements:

• Must have a column with Date data type
• Must have no gaps (every day between min and max must exist)
• Must be marked as a date table

Common trap: a table with only month-end dates (Jan 31, Feb 28, Mar 31) is NOT valid because it has gaps between months.

Configuring table and column properties

Beyond dates, Power BI lets you configure properties on any table and column to improve the user experience.

Column properties

Property	What It Does	Example
Data Category	Tells Power BI how to treat the column (URL, Image URL, Barcode, Geographic)	Setting a column to “City” enables automatic map visualisation
Sort By Column	Sorts one column by another column’s values	Sort “Month Name” by “Month Number” (so Jan comes before Feb, not after Apr)
Summarize By	Sets the default aggregation	ProductID should be “Don’t Summarize” (it’s a key, not a measure)
Display Folder	Groups columns into folders in the Fields pane	Group Year, Quarter, Month into a “Date Attributes” folder
Description	Adds a tooltip when hovering over the field	”Total revenue including tax”
Is Hidden	Hides the column from report authors	Hide foreign key columns that aren’t useful for reporting

Sort By Column: the classic exam question

Riley at Coastal Fresh (🛒) has a Month Name column: January, February, March… By default, Power BI sorts text alphabetically — so April comes first, not January.

Fix: Create a MonthNumber column (1-12) and set Sort By Column on Month Name to MonthNumber.

Nadia at Prism Agency (📊) has the same issue with day names — Friday sorts before Monday. She creates a DayOfWeekNumber column and sorts DayName by it.

Deep dive: data categories for maps

Setting a column’s data category to a geographic type (Country, State, City, Postal Code, Latitude, Longitude) enables:

• Automatic map visual suggestions
• Better geocoding accuracy
• Bing Maps integration

Dr. Ethan at Bayview Medical (🏥) sets his hospital City column’s data category to “City” so Power BI automatically places hospitals on map visuals.

Knowledge check

Question

What three requirements make a valid date table in Power BI?

Click or press Enter to reveal answer

Answer

1. Has a Date column with Date data type. 2. Contains contiguous dates (no gaps). 3. Is marked as a date table using Table tools → Mark as date table.

Click to flip back

Question

What DAX function auto-detects your data's date range for a calendar table?

Click or press Enter to reveal answer

Answer

CALENDARAUTO() — it scans all date columns in your model and creates a contiguous date range covering min to max dates found.

Click to flip back

Question

How do you fix month names sorting alphabetically instead of chronologically?

Click or press Enter to reveal answer

Answer

Create a MonthNumber column (1-12), then set the Month Name column's 'Sort By Column' property to MonthNumber.

Click to flip back

Knowledge Check

Riley creates a date table using CALENDARAUTO() and adds Year, Month, and Quarter columns. Time intelligence DAX functions still don't work. What did she miss?

AShe needs to add a DayOfWeek columnBShe forgot to mark the table as a date tableCCALENDARAUTO doesn't support time intelligenceDShe needs to create relationships to all fact tables

Check Answer

Knowledge Check

Nadia's report shows months sorted as: April, August, December, February... What property should she configure?

AData Category → MonthBSort By Column → set Month Name to sort by MonthNumberCSummarize By → Don't SummarizeDDisplay Folder → Date Attributes

Check Answer

Next up: Columns vs Measures: When to Use Which — the most important decision in DAX.