How to Model a Date Dimension for Time Intelligence in Power BI
You are building a star schema in Power BI. Your fact table contains sales data at the transaction level. Which of the following is the best practice for modeling the date dimension to support time intelligence functions like year-to-date (YTD) calculations?
Quick Answer
The answer is to create a date table with a contiguous range of dates and mark it as a date table. This is the best practice for modeling a date dimension for time intelligence because Power BI’s time intelligence functions, such as year-to-date (YTD) calculations, require a continuous, complete set of dates to correctly aggregate across periods, including days with no transactions. On the Microsoft Power BI Data Analyst PL-300 exam, this concept tests your understanding of star schema design and the necessity of a dedicated date table over relying on a single date column in the fact table. A common trap is assuming a date table covering only sales dates is sufficient, but that would break YTD totals by missing gaps. Remember the memory tip: “Contiguous dates, not just sales dates—mark it as a date table to unlock time intelligence.”
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a date table with a contiguous range of dates and mark it as a date table
In Power BI, to enable time intelligence functions (like YTD, QTD, etc.), a dedicated date table with a contiguous range of dates is required. This table must be marked as a date table in the model. Option A is incorrect because using the fact table's date column directly lacks the necessary date hierarchy and attributes. Option B is incorrect because a date table that only includes dates with transactions will have gaps, leading to incorrect time intelligence calculations. Option C is incorrect because having multiple date columns in the fact table is not a replacement for a proper date dimension; instead, role-playing dimensions should be used. Option D is correct as it describes the best practice: a contiguous date table marked as a date table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the fact table's date column directly in measures without a date table
Why it's wrong here
Without a date table, time intelligence functions may not work correctly.
- ✗
Create a date table with only the dates that appear in the fact table
Why it's wrong here
A date table must have a contiguous range of dates to support time intelligence.
- ✗
Use multiple date columns (e.g., OrderDate, ShipDate) in the fact table
Why it's wrong here
Multiple date columns in the fact table are acceptable, but they do not replace the need for a proper date dimension.
- ✓
Create a date table with a contiguous range of dates and mark it as a date table
Why this is correct
This enables time intelligence functions and proper filtering.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are building a Power BI semantic model that includes a Date table. Which of the following is a best practice for creating a Date table?
easy- ✓ A.Mark the date table as a date table in Power BI Desktop.
- B.Create relationships from the date table to every fact table column that contains dates.
- C.Use the CALENDARAUTO function to automatically generate dates.
- D.Use a date table that includes only dates that exist in the fact tables.
Why A: Marking a date table as a date table in Power BI Desktop enables time intelligence functions and ensures the table is used as the source for date hierarchies. Option B is incorrect because you should not create relationships to every date column; instead, have one active relationship per fact table and use inactive relationships with USERELATIONSHIP as needed. Option C is incorrect because CALENDARAUTO can generate an unpredictable date range that may not cover all necessary dates; it is better to use CALENDAR or CALENDARPERIOD with explicit start and end dates. Option D is incorrect because the date table should contain a contiguous range of dates that covers all dates in the fact tables, not only those that exist in the data.
Variation 2. You are building a star schema model in Power BI. You have a fact table of sales transactions and dimension tables for Date, Customer, Product, and Store. The Date table contains a column 'FiscalYear' that you want to use for time intelligence calculations. What is the best practice for handling the Date relationship?
medium- A.Create a separate fiscal date table and relate it to the fact table using the FiscalYear column.
- B.Use the built-in DATESYTD function directly on the OrderDate column from the fact table.
- C.Create a composite key using FiscalYear and Quarter columns in the Date table and relate to the fact table.
- ✓ D.Mark the Date table as a date table using the Calendar icon in the Table tools ribbon and set a relationship on the Date column.
Why D: Marking the Date table as a date table (via the Calendar icon in Table tools) and creating a relationship on the Date column is the best practice for time intelligence in Power BI. This ensures that DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly by using a single, continuous date column that aligns with the fact table's date column. It also avoids the need for composite keys or separate fiscal tables, maintaining a clean star schema.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.