Model the data · Topic 2 of 6
Date Tables, Calculated Columns and Calculated Tables
PL-300 date tables and calculations: create a common date table, mark it as a date table, and decide between calculated columns, calculated tables and measures.
- 4 min
- A date table has one row per day, no gaps, a Date type column, and is marked as the date table.
- CALENDAR uses start and end dates. CALENDARAUTO finds the range from the model.
- Calculated columns and tables are computed at refresh and stored. Measures are computed at query time.
What it is
Time intelligence needs a proper date table. You build one with DAX or Power Query, mark it, and relate it to your fact tables. Calculated columns and calculated tables are DAX results that are computed when the model refreshes and stored in the model. Knowing when to use them, and when a measure or a Power Query step is better, is a frequent exam theme.
How it works
A common date table
A date table has one row for every day, with no gaps, covering whole years. It has columns such as Year, Quarter, Month number, Month name and Weekday. Use one date table for the whole model, related to each fact table date.
Ways to create one:
- DAX CALENDAR takes a start and an end date:
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) ). - DAX CALENDARAUTO scans the model for the earliest and latest dates and returns whole years.
- Power Query, with a list of dates and a conversion to a table.
- A date dimension from the data warehouse. This is the best option if one exists.
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" )
)Select the table and choose Mark as date table, then pick the Date column. It must be of Date type, with unique values and no blanks. Marking tells Power BI that time intelligence functions can rely on this table.
Auto date/time
By default, Power BI creates hidden date tables for each date column, called auto date/time. They help beginners but enlarge the model and cannot be shared. In a model with your own date table, turn the option off in Options under Data load, for the current file or globally.
Calculated columns
A calculated column is a DAX expression evaluated row by row in the table's own row context. The result is stored and uses memory.
Margin = Sales[Revenue] - Sales[Cost]
Category Name = RELATED ( Product[Category] )Use a calculated column when you need to:
- Slice, filter or group by the value, for example a size band.
- Use it as a key in a relationship.
- Bring a value from a related table with RELATED.
Calculated tables
A calculated table is a table built by a DAX expression and recalculated at refresh.
| Use | Example |
|---|---|
| A date table | CALENDAR or CALENDARAUTO |
| A role playing copy of a dimension | Ship Date = 'Date' |
| A small lookup | DATATABLE, or GENERATESERIES for a number list |
| A summary table | SUMMARIZE or ADDCOLUMNS over a fact table |
Which to choose
| Need | Choose |
|---|---|
| A value shown at different levels, depending on filters, such as total sales | Measure |
| A value to slice by, or use in a relationship | Calculated column or a Power Query column |
| Shape or clean data before loading | Power Query |
| A whole new table from other tables | Calculated table |
A measure is computed when a visual asks for it and uses no storage. A calculated column uses memory for every row. Prefer Power Query for columns that do not need the model, because the work is done once in the source query and compresses better.
A worked scenario
A retailer's model has Sales dates from 2022 to 2025 and no date table. The analyst creates Date with CALENDARAUTO and adds Year and Month columns, marks it as the date table, and relates Date to Sales on OrderDate. She turns off auto date/time. She adds a calculated column Price Band to Product, because the report needs a slicer by band, but writes Total Margin as a measure, because it must change with filters.
What to remember for the exam
- A date table has one row per day, no gaps, a Date type column, and is marked as the date table.
- CALENDAR uses start and end dates. CALENDARAUTO finds the range from the model.
- Calculated columns and tables are computed at refresh and stored. Measures are computed at query time.
- Use a calculated column to slice, filter or relate. Use a measure for aggregations.
- Turn off auto date/time when you use your own date table.
If a time intelligence formula returns the wrong period, check that the date table is marked and has no gaps.
How the exam asks about this
- "Time intelligence functions give wrong results." Mark a complete date table.
- "A value must appear as a slicer." Calculated column.
- "A ratio must work at any filter level." Measure.
- "Create a second copy of the date table for ship dates." A calculated table.
Common mistakes
Using the fact table's date column directly. Gaps cause wrong periods.
A calculated column for a total. It cannot respond to filters.
Leaving auto date/time on. The model grows with hidden tables.
Several date tables with unrelated roles. Use role playing relationships.
Check yourself
What must a date table contain?
One row for every day, with no gaps, in a column of Date type.
Which is stored in the model, a measure or a calculated column?
The calculated column.
Which function builds a date range from the dates already in the model?
CALENDARAUTO.
Topic 2 of 6 in Model the data
Test yourself on Model the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.