Microsoft Power BI Quiz: Date Tables
10 questions · exam conditions
0:00
Date TablesQuestion 1 of 10

The Sales[OrderDateTime] column contains values such as 2026-03-15 14:30:00. A marked Date table contains one row per day, and Date[Date] stores each date at midnight. A relationship directly between these columns leaves most sales rows unmatched.

Which modification should you make?

Create a date-only column in Sales, relate it to Date[Date], and keep the Date table marked.
Add every transaction timestamp to the Date table, relate the timestamps, and mark that timestamp column.
Convert Date[Date] to text, relate it to the formatted transaction timestamp, and retain the marked table.
Keep the current relationship, enable bidirectional filtering, and use the automatic date hierarchy in measures.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Date Tables

Practice Date Tables in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Date Tables, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The Sales[OrderDateTime] column contains values such as 2026-03-15 14:30:00. A marked Date table contains one row per day, and Date[Date] stores each date at midnight. A relationship directly between these columns leaves most sales rows unmatched.

Which modification should you make?

  1. Create a date-only column in Sales, relate it to Date[Date], and keep the Date table marked. (correct answer)
  2. Add every transaction timestamp to the Date table, relate the timestamps, and mark that timestamp column.
  3. Convert Date[Date] to text, relate it to the formatted transaction timestamp, and retain the marked table.
  4. Keep the current relationship, enable bidirectional filtering, and use the automatic date hierarchy in measures.
Explanation: Whenever you see a question about relating a Date table to a fact table in Power BI, focus on granularity matching — the two sides of a relationship must contain comparable values for joins to work correctly. A marked Date table requires exactly one row per calendar day, with each date stored at midnight (e.g., 2026-03-15 00:00:00). When Sales[OrderDateTime] stores full timestamps like 2026-03-15 14:30:00, no timestamp matches midnight exactly, so the relationship produces blanks. The fix in A is correct: you create a calculated column in Sales that strips the time component — using DATE(), DATEVALUE(), or INT() — producing a pure date value that matches Date[Date] exactly. This preserves the marked Date table, maintains time intelligence functions, and resolves the granularity mismatch cleanly. B is wrong because adding every transaction timestamp to the Date table violates the one-row-per-day requirement, breaks the "marked as Date table" designation, and destroys time intelligence entirely. C is wrong because converting dates to text for matching is fragile, locale-dependent, and eliminates the ability to use DAX time intelligence functions, which require proper date data types. D is wrong because bidirectional filtering addresses cross-filter direction, not granularity mismatches — the rows still don't join, so enabling it changes nothing about the unmatched records, and the automatic date hierarchy won't compensate for a broken relationship. As a study rule: in Power BI, relationship problems caused by time components are always solved on the fact table side — strip the time to create a date key, never alter the Date table's structure.

Question 2

A model has a marked table named DimDate and an active one-to-many relationship from DimDate[Date] to Sales[OrderDate]. A prior-year measure applies SAMEPERIODLASTYEAR to Sales[OrderDate] rather than to the date dimension.

What change most directly aligns the measure with the marked Date table?

  1. Apply SAMEPERIODLASTYEAR to DimDate[Date] and retain the existing active relationship. (correct answer)
  2. Mark the Sales fact table as a Date table by using Sales[OrderDate].
  3. Change the relationship to bidirectional and continue using Sales[OrderDate] in the measure.
  4. Remove DimDate and rely on the automatic hierarchy for Sales[OrderDate].
Explanation: When working with time intelligence functions in Power BI, the key principle is that these functions are designed to work with the marked Date table — not bypass it. A marked Date table tells the model which table and column represent the authoritative date spine, and time intelligence functions like SAMEPERIODLASTYEAR are built to operate through that relationship. The most direct fix is A: apply SAMEPERIODLASTYEAR to DimDate[Date] while keeping the existing active relationship intact. Since DimDate is already marked as the Date table and has an active relationship to Sales[OrderDate], the engine will correctly filter the fact table through that relationship. Using the dimension's date column is exactly what the marked Date table pattern is designed for — you get consistent, reliable time intelligence behavior. B is wrong because marking the fact table (Sales) as a Date table would break the intended star-schema design. Fact tables shouldn't serve as Date tables; that role belongs to a dedicated dimension. C is a red herring — making the relationship bidirectional changes cross-filter direction but doesn't fix the root issue of using the wrong column in the measure. Bidirectional relationships also introduce ambiguity risks in complex models. D is the worst option: removing DimDate and relying on auto-hierarchies sacrifices the precision and control that a proper Date table provides, and time intelligence functions won't behave predictably without one. As a study tip, remember: time intelligence functions should always reference the marked Date table's date column. If a measure is pointing to a fact table column instead, that's your signal to redirect it to the dimension.

Question 3

A Sales table contains OrderDate and ShipDate. Report users must independently filter one visual by order year and another visual by ship year on the same report page. Both date contexts must work without measure-specific relationship activation.

How should you model the date dimensions?

  1. Use one marked Date table with two active relationships from Date to the Sales table.
  2. Use one marked Date table with one inactive relationship and enable bidirectional filtering on both relationships.
  3. Create Order Date and Ship Date role-playing tables, mark both, and give each an active relationship. (correct answer)
  4. Place both fact date columns in one calendar table and mark the table by using both columns.
Explanation: When a fact table contains multiple date foreign keys — like OrderDate and ShipDate — you're dealing with a classic role-playing dimension scenario. The key question is: how do you give each date column its own independent filter context without conflicts? In Power BI, a single table can only have one active relationship per pair of tables. This is the core constraint that shapes your entire modeling decision here. The cleanest solution is to create separate physical copies of your Date table — one called "Order Date" and one called "Ship Date" — each marked as a Date table, and each connected to the Sales table via its own active relationship. This is exactly what option C describes. Because both relationships are active, slicers or filters placed on either table work independently and simultaneously, with no DAX workarounds needed. Option A is impossible — Power BI simply won't allow two active relationships between the same pair of tables. Option B relies on one inactive relationship, which means you'd need USERELATIONSHIP() inside every measure that uses the inactive path — violating the requirement that date contexts work "without measure-specific relationship activation." Option D is a fabrication; the Mark as Date Table feature accepts only a single date column as the key, so marking by two columns isn't a valid Power BI feature. The correct answer is C: role-playing tables give each date context a clean, active relationship with no hidden complexity. Study tip: Whenever you see multiple date columns in a fact table, immediately think "role-playing dimensions." Each date role needs its own physical Date table copy in Power BI — one active relationship per table pair is a hard rule you can rely on.

Question 4

A star schema uses integer keys. DimDate[DateKey] contains values such as 20260709 and relates to Sales[DateKey]. The dimension also contains a unique, contiguous DimDate[Date] column with the Date data type.

How should you configure the model for standard time intelligence?

  1. Mark DimDate by using DimDate[Date] while retaining the relationship based on the integer date keys. (correct answer)
  2. Mark DimDate by using DimDate[DateKey] because the relationship key must be the designated column.
  3. Mark the Sales table by using Sales[DateKey] because time intelligence must use the fact-side key.
  4. Replace DimDate[Date] with formatted text because marked tables cannot retain surrogate integer keys.
Explanation: When working with time intelligence in Power BI, questions about "Mark as Date Table" test a specific set of requirements: the designated column must have the Date data type, contain unique values, and have no gaps. Critically, the marked column does not need to be the column used in the model relationship. This is exactly why A is correct. You mark DimDate using DimDate[Date] because it satisfies all three requirements — it's a proper Date type, unique, and contiguous. Meanwhile, the integer surrogate key relationship between DimDate[DateKey] and Sales[DateKey] remains intact and continues to drive join performance. Power BI separates the concept of "which column drives time intelligence functions" from "which column links tables together." These can — and often should — be different columns. B reflects a common misconception: that the relationship key must serve as the marked column. Power BI imposes no such constraint. The relationship and the marked date column are independent configurations. C is wrong on two counts. First, you mark a dimension table, never a fact table. Second, Sales[DateKey] is an integer, not a Date type, so it cannot satisfy the marking requirements regardless. D is a fabricated limitation. Marking a table as a Date Table places no restrictions on other columns — you can keep integer keys, text fields, or any other columns alongside the designated Date column. Your study tip: remember that "Mark as Date Table" is about unlocking DAX time intelligence functions, and the marked column is a pointer for DAX, not a replacement for your relationship key.

Question 5

A marked Date table is currently generated from the minimum and maximum dates in ActualSales. The model now includes monthly budget rows through December 2027, but actual sales currently end in July 2026. Reports must display future budget months even when no actual sales exist.

What should you change in the Date table?

  1. Relate Budget directly to ActualSales by month so future budget dates inherit actual-sales filtering.
  2. Keep the current calendar and enable Show items with no data on the budget visuals.
  3. Append only the twelve month-end budget dates and mark the resulting date column again.
  4. Extend the calendar through the latest budget date, preserve daily rows, and keep the date column marked. (correct answer)
Explanation: When working with Date tables in Power BI, the golden rule is that your calendar must span every date that any fact table references — including future dates. If your Date table stops before the latest date in any related table, those rows become orphaned and won't filter correctly in reports. Because budget data extends through December 2027 while actual sales end in July 2026, the Date table must cover the full range through December 2027. The correct approach, choice D, extends the calendar with daily rows all the way through the latest budget date while keeping the date column marked as the official Date table. Daily granularity ensures that any future drill-down or time intelligence function (like DATEADD or TOTALYTD) works correctly. Preserving the marked status tells Power BI this is the authoritative date axis for all relationships. Choice A is a relationship mistake — connecting Budget directly to ActualSales conflates two separate fact tables and breaks the star schema. Budget and ActualSales should both relate independently to the Date dimension, not to each other. Choice B misunderstands the root problem: "Show items with no data" reveals dimension members with no matches, but if future dates don't even exist in the Date table, there's nothing to show — the dates are simply absent, not merely unmatched. Choice C is tempting but dangerous: appending only twelve month-end rows creates gaps in the calendar, breaking daily-granularity time intelligence calculations and any measures that rely on contiguous dates. As a study tip, remember: in Power BI, a marked Date table must be contiguous and complete across all dates referenced anywhere in the model — past or future.

Question 6

A shared semantic model contains several fact-table date columns. Auto date/time is enabled, so report authors see separate hidden hierarchies for those columns. You have added a conformed calendar with fiscal periods, week labels, and holiday indicators.

Which configuration best promotes consistent time intelligence across reports?

  1. Leave the calendar unmarked and copy its fiscal columns into each fact table for report authors.
  2. Leave Auto date/time enabled and use each fact column's hidden hierarchy for fiscal calculations.
  3. Mark every fact table by using its transaction date and retain the hidden automatic hierarchies.
  4. Mark the conformed calendar, disable Auto date/time, and use calendar attributes in slicers and measures. (correct answer)
Explanation: When you see a question about time intelligence consistency in Power BI, focus on two principles: a single source of truth for dates, and eliminating competing hierarchies that create conflicting calculations across reports. A conformed calendar table — one shared table with fiscal periods, week labels, and holiday flags — is the gold standard for enterprise-wide consistency. To unlock it as the authoritative date source, you mark it as a Date Table. This tells Power BI's engine to use it exclusively for time intelligence functions like TOTALYTD and DATESYTD, ensuring every measure uses the same fiscal logic. Pairing this with disabling Auto date/time removes the hidden per-column hierarchies that Power BI generates automatically. Those hidden hierarchies are calendar-year-based and completely ignore your fiscal periods — leaving them active means report authors can accidentally use the wrong hierarchy, producing inconsistent results. Answer D is correct because it eliminates that ambiguity entirely. Answer A fails because copying fiscal columns into each fact table defeats the "single source of truth" purpose — updates must be replicated everywhere, and inconsistencies creep in. Answer B is the most dangerous trap: Auto date/time hierarchies are calendar-year only and cannot represent fiscal quarters or custom week labels, so they are structurally incompatible with fiscal time intelligence. Answer C compounds this problem — marking multiple fact tables as Date Tables creates ambiguous relationships and still leaves conflicting automatic hierarchies active. As a study tip, remember this pattern: conformed calendar + Mark as Date Table + disable Auto date/time = consistent time intelligence. Any question describing a shared semantic model with custom fiscal logic should point you toward this combination immediately.

Question 7

A properly marked Date table spans complete fiscal years, and the company's fiscal year ends September 30. A developer assumes that marking the table will cause a default year-to-date calculation to reset automatically on October 1.

What should the developer do to produce the required fiscal year-to-date result?

  1. Use the default year-to-date function because marking the table automatically infers its fiscal boundary.
  2. Use the marked date column and explicitly specify September 30 in the year-to-date calculation. (correct answer)
  3. Unmark the Date table and calculate year-to-date by filtering only the fiscal-year label column.
  4. Recreate the table with CALENDARAUTO(9) because its range alone changes every default year-to-date boundary.
Explanation: Whenever you see a question combining marked Date tables with fiscal year-to-date calculations in Power BI, remember that marking a table and configuring time intelligence boundaries are two separate concerns. Marking a Date table tells Power BI that the table is your authoritative calendar reference, enabling built-in time intelligence functions. However, marking does not teach DAX anything about where your fiscal year begins or ends. Functions like TOTALYTD default to December 31 as the year-end boundary unless you explicitly override it. To calculate fiscal year-to-date for a September 30 year-end, you must pass that date as the optional third argument: TOTALYTD([Measure], Dates[Date], "9/30"). This is exactly what B prescribes — using the marked date column while explicitly supplying September 30 as the reset boundary. A is wrong because it describes behavior that simply doesn't exist. Marking a Date table conveys no fiscal boundary information to DAX; the engine never "infers" a non-calendar year-end automatically. C is a trap for developers who confuse filtering by a label column with proper time intelligence. Unmarking the table disables the time intelligence framework entirely, and filtering on a label column produces fragile, non-standard calculations that won't leverage DAX's optimized engine. D misrepresents what CALENDARAUTO(9) does. The argument controls the last month of the auto-generated range, ensuring complete years are included — it does not change the default year-to-date reset boundary in any DAX calculation. As a study tip: always ask two separate questions — "Is my Date table marked?" and "Have I told DAX where my fiscal year ends?" Both must be answered correctly for fiscal time intelligence to work.

Question 8

The earliest date in a model is January 15, 2024, and the latest date is August 20, 2025. The organization's fiscal years begin July 1 and end June 30. You create the calendar by using CALENDARAUTO(6).

Which date range should the generated table contain before you mark its date column?

  1. January 1, 2024 through December 31, 2025, because automatic calendars always use calendar years.
  2. July 1, 2023 through June 30, 2026, because both boundary dates are expanded to full fiscal years. (correct answer)
  3. July 1, 2024 through June 30, 2025, because only the first complete fiscal year is included.
  4. January 15, 2024 through August 20, 2025, because only dates present in the model are generated.
Explanation: Whenever you see a question involving CALENDARAUTO(), focus on two things: what the optional parameter means, and how Power BI determines the date range boundaries. The optional parameter in CALENDARAUTO(6) specifies the fiscal year-end month — in this case, month 6 (June). Power BI uses this to identify which fiscal year each boundary date falls in, then expands the range to cover complete fiscal years. The earliest date in the model is January 15, 2024, which falls within the fiscal year running July 1, 2023 – June 30, 2024. The latest date is August 20, 2025, which falls within the fiscal year running July 1, 2025 – June 30, 2026. So the generated table spans July 1, 2023 through June 30, 2026 — confirming B is correct. A is wrong because CALENDARAUTO() does not always use calendar years. Without a parameter (or with parameter 12), it defaults to December as the year-end, producing calendar years — but supplying a different month changes that behavior entirely. C is wrong because it only covers a single fiscal year and ignores both the earliest and latest dates in the model. Power BI always expands outward to fully contain every boundary date, not inward. D is wrong because CALENDARAUTO() never just returns the raw dates present in your data. Its entire purpose is to generate a complete, contiguous date range — filling in every day between the boundaries. Study tip: Memorize that the CALENDARAUTO(n) parameter sets the fiscal year-end month, and that Power BI always rounds the range outward to complete fiscal (or calendar) years — never inward, never exact.

Question 9

A calendar table starts on January 1, 2024 and ends on December 31, 2024. Its date column is unique, has the Date data type, and contains no blanks. Power BI still reports that the column is not contiguous when you mark the table.

Which correction is most likely required?

  1. Convert the date values to Date/Time so Power BI can infer the missing leap-day timestamp.
  2. Remove December 31, 2024 so the table has the same number of days as a standard year.
  3. Add February 29, 2024 to the table so every day in the leap year is present. (correct answer)
  4. Add a duplicate January 1, 2024 row so the table explicitly identifies the year boundary.
Explanation: Whenever you see a question about marking a table as a Date Table in Power BI, the key concept to focus on is contiguity — the requirement that your date column contains every single calendar day within its range, with no gaps whatsoever. 2024 is a leap year, meaning it has 366 days, not 365. When Power BI validates a marked Date Table, it checks that the date column forms an unbroken sequence from the first date to the last. If your table runs January 1 through December 31, 2024, but you built it assuming a standard 365-day year, February 29, 2024 is silently missing. Power BI detects this gap and flags the column as non-contiguous — even though uniqueness and data type requirements are otherwise satisfied. Adding February 29, 2024 closes that gap and satisfies the contiguity requirement, making C the correct fix. A is wrong because the data type requirement for a marked Date Table is simply Date — not Date/Time. Converting to Date/Time changes nothing about whether days are missing and does not resolve a contiguity error. B is a trap that misunderstands the problem. Removing December 31 would actually create a new gap rather than fix anything; Power BI needs all 366 days present, not fewer days to match a standard year. D is wrong because duplicate rows would violate the uniqueness requirement, causing a different validation error entirely — not correct the contiguity issue. As a study habit, always ask yourself: "Is this year a leap year?" when reviewing calendar table questions on the Power BI exam. Leap-year gaps are a classic source of contiguity failures.

Question 10

A sales model contains transactions only on business days. A calculated table is created by using DISTINCT(Sales[OrderDate]). The table includes unique, nonblank dates, but Power BI rejects it when you attempt to mark it as a Date table.

What should you do to create a valid Date table while retaining the required reporting period?

  1. Convert the distinct order-date column to Date/Time, and then mark the existing table by using that column.
  2. Create a table with CALENDAR from the minimum through maximum order date, and mark its date column. (correct answer)
  3. Add a sequential index to the existing table, and mark the table by using the index column.
  4. Enable Auto date/time for the order-date column, and mark the existing table by using that hierarchy.
Explanation: When Power BI validates a Date table, it enforces one strict rule: the designated date column must contain a contiguous, uninterrupted sequence of dates with no gaps and no duplicates. This is the core concept being tested here. A table derived purely from transaction dates will have gaps wherever no business occurred — weekends, holidays — and Power BI will reject it immediately. Option B is correct because CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate])) generates every single date between the earliest and latest order date, with no gaps. This satisfies Power BI's contiguity requirement, and you can then mark that table's date column as the official Date table. Your visuals still reflect only the reporting period defined by your actual sales data — you haven't lost anything meaningful. Option A fails because changing the data type to Date/Time doesn't fix the underlying gap problem. Power BI's validation checks for missing dates, not data types — a column full of business-day-only dates will still be rejected regardless of formatting. Option C is a red herring. An index column is numeric, not a date type. Power BI requires a date column to mark a Date table, so an integer index is categorically ineligible for that role. Option D misunderstands what Auto date/time does. That feature auto-generates hidden date hierarchies for existing date columns — it doesn't create a standalone, markable Date table, and it can't be used to satisfy the Mark as Date Table requirement on your own calculated table. Your study tip: memorize that a valid Power BI Date table needs gapless, contiguous dates — always reach for CALENDAR or CALENDARAUTO when building one from scratch.