PL-300 Prepare the data Practice Question
You are designing a Power BI semantic model that uses a large fact table from Azure SQL Database. The table includes a date column. You need to ensure that the model supports time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. What is the recommended approach?
⚠ Common exam trap
Many exam-takers think auto-date/time or the built-in date hierarchy is sufficient, but Microsoft explicitly recommends creating and marking a separate date table for reliable time intelligence, especially when using large fact tables with non-contiguous dates.
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 separate date table and mark it as a date table in the model.
Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require a separate date table with a contiguous date range marked as the date table in the model. This ensures that DAX can correctly calculate time-based aggregations across all dates, even if the fact table has gaps or missing dates. Without a marked date table, these functions may return incorrect or blank results.
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 'Add Calendar' function in Power Query and rely on auto-date/time.
Why it's wrong here
Using the 'Add Calendar' function in Power Query creates a date dimension, but merely creating it does not automatically mark it as the date table for the model. Relying on auto-date/time is problematic because Power BI Desktop has this feature disabled by default, and the hidden auto-date hierarchies are not recognized by DAX time intelligence functions like TOTALYTD or SAMEPERIODLASTYEAR. Without explicitly marking the generated calendar as a date table, these functions will return errors or incorrect results, so this approach is not a valid substitute.
- ✗
Use DirectQuery mode and rely on the SQL Server date functions.
Why it's wrong here
DirectQuery mode forwards queries to SQL Server, but DAX time intelligence functions are evaluated by the Power BI engine, not translated to T-SQL. Even if you rely on SQL Server scalar functions in DAX, the core time intelligence functions require a separate date table marked as a date table in the model. Without that marked table, calculations such as DATESYTD or NEXTDAY will fail or produce unexpected results, and DirectQuery does not simplify the requirement—it still demands a canonical date table for consistent time-period calculations.
- ✗
Use the built-in date hierarchy from the fact table's date column.
Why it's wrong here
The built-in date hierarchy that appears when you drop a date column into a visual is an auto-generated grouping for drill-down (Year, Quarter, Month, Day), but it is not a discrete date table. Time intelligence functions such as DATEADD and PARALLELPERIOD require a separate table marked as a date table because they need to enumerate a contiguous set of dates to shift periods and identify ranges. This built-in hierarchy is unaware of the calendar's continuous structure and lacks a designated relationship with the fact table, so it cannot serve as the date dimension for DAX time intelligence.
- ✓
Create a separate date table and mark it as a date table in the model.
Why this is correct
Creating a separate date table and marking it as a date table is the correct approach because it establishes a continuous, non-blank range of dates that the DAX engine explicitly recognizes for time intelligence. Marking the table via 'Mark as Date Table' sets the ‘Date’ column as the authoritative calendar reference, which enables functions like TOTALYTD, PREVIOUSYEAR, and PARALLELPERIOD to correctly compute period boundaries and offsets. This practice also supports fiscal calendars, holidays, and custom hierarchies, making it the recommended design pattern in Power BI for any model requiring robust date analysis.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
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.