Microsoft Power BI Quiz: Ytd Mtd Qtd Measures
10 questions · exam conditions
0:00
Ytd Mtd Qtd MeasuresQuestion 1 of 10

A report page is filtered to product category Bikes and to dates through August 20, 2025. The model uses a marked date table and the following measure:

Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date]))

What does the measure return under this filter context?

Sales for all product categories from January 1 through August 20, because CALCULATE removes every existing filter
Bike sales for August 1 through August 20, because the category filter limits the accumulation to the current month
Bike sales from January 1 through August 20, because the date context expands while the category filter remains
Bike sales from January 1 through August 31, because DATESYTD completes the latest visible month
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Ytd Mtd Qtd Measures

Practice Ytd Mtd Qtd Measures 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 Ytd Mtd Qtd Measures, 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

A report page is filtered to product category Bikes and to dates through August 20, 2025. The model uses a marked date table and the following measure:

Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date]))

What does the measure return under this filter context?

  1. Sales for all product categories from January 1 through August 20, because CALCULATE removes every existing filter
  2. Bike sales for August 1 through August 20, because the category filter limits the accumulation to the current month
  3. Bike sales from January 1 through August 20, because the date context expands while the category filter remains (correct answer)
  4. Bike sales from January 1 through August 31, because DATESYTD completes the latest visible month
Explanation: When you see a measure built around DATESYTD inside CALCULATE, the key question to ask is: which filters does CALCULATE replace versus which does it leave alone? DATESYTD is a time intelligence function that generates a new set of dates — from January 1 of the current year through the last visible date. When CALCULATE applies this new date set, it replaces the existing date filter on 'Date'[Date], expanding the window back to January 1. However, CALCULATE only overrides filters on the columns it explicitly addresses. Because DATESYTD touches only the date column, the product category filter (Bikes) remains fully intact. That's exactly why C is correct: the measure returns Bike sales from January 1 through August 20. The date context expands via DATESYTD, but the category filter is untouched and continues to restrict results to Bikes. A is wrong because CALCULATE does not remove every filter — it only modifies filters on columns addressed by its filter arguments. The category filter survives. B is wrong on two counts: DATESYTD accumulates from the start of the year, not the start of the current month, and the category filter does not change the accumulation period at all. D is wrong because DATESYTD uses the last visible date as its endpoint — August 20 here — not the end of the month. You'd need DATESMTD or an ENDOFMONTH variant to complete the month. As a study tip, remember: time intelligence functions in CALCULATE replace the date filter only — all other filters in context ride along unchanged.

Question 2

The Date table extends through December 31, 2026, but the Sales table contains transactions only through July 18, 2026. A card has no date filter and uses this measure:

Sales MTD = CALCULATE([Net Sales], DATESMTD('Date'[Date]))

The card returns blank even though July contains sales.

What is the best explanation for the blank result?

  1. DATESMTD ignores dates beyond the latest transaction and therefore evaluates only the single date July 18
  2. DATESMTD requires the transaction date from the fact table and cannot operate on a separate calendar table
  3. DATESMTD returns values only after a month is complete, so July sales remain excluded until July 31
  4. DATESMTD anchors to December 31 from the unfiltered calendar, so it evaluates December dates that have no sales (correct answer)
Explanation: When using time intelligence functions in DAX, always ask yourself: what filter context is currently active? With no date slicer or filter on the card, the Date table is completely unfiltered — meaning the "current" date context spans the entire table, all the way through December 31, 2026. DATESMTD works by looking at the last date in the current filter context and returning all dates from the start of that month through that last date. When the Date table is unfiltered, the last visible date is December 31, 2026 — so DATESMTD returns every day in December 2026. Since the Sales table has no transactions in December 2026, the measure returns blank. That's exactly what answer D describes, and it's the correct explanation. Answer A is wrong because DATESMTD does not inspect the fact table to find the latest transaction date — it anchors purely to the last date in the dimension table's current filter context. Answer B is incorrect because DATESMTD is specifically designed to work with a separate, dedicated Date table; that's the recommended best practice in Power BI data modeling. Answer C is a complete fabrication — DATESMTD has no concept of waiting for a month to finish; it returns a partial month range by design. The key study tip here: time intelligence functions anchor to your Date table, not your fact table. A common fix for this scenario is wrapping your measure with LASTDATE('Sales'[Date]) or ensuring a date slicer is always present so the calendar is filtered to a meaningful range before DATESMTD evaluates.

Question 3

A matrix has Year, Quarter, and Month on rows. For the second quarter of 2025, monthly net sales are April: 80, May: 100, and June: 120. The quarter includes all dates through June 30. The matrix uses:

Sales MTD = CALCULATE([Net Sales], DATESMTD('Date'[Date]))

What value should the measure return at the second-quarter subtotal?

  1. 300, because the subtotal adds the three displayed month-to-date values
  2. 120, because June 30 is the latest date and the MTD range is June 1 through June 30 (correct answer)
  3. 100, because the subtotal averages the month-to-date values within the quarter
  4. The YTD total through June 30, because quarter subtotals automatically expand MTD into YTD
Explanation: When a matrix displays a subtotal row, Power BI evaluates your measure in a filter context that includes all rows belonging to that group — not just the visible leaf-level rows. For a quarter subtotal, the filter context contains every date in that quarter, not just the last month's dates. DATESMTD always returns dates from the first day of the month through the last date in the current filter context. When the Q2 subtotal is evaluated, the filter context spans April 1 through June 30. The "last date" in that context is June 30, so DATESMTD returns June 1–June 30 — exactly one month. Applying [Net Sales] over that range produces 120, which is June's monthly total. That confirms B is correct. A is wrong because Power BI does not simply add the displayed leaf-level values to form a subtotal. The measure is re-evaluated fresh in the subtotal's own filter context, and there is no additive rollup of MTD values happening automatically. C is wrong for the same fundamental reason — there is no averaging mechanism built into subtotal evaluation. Power BI does not average child-row measure results; it recalculates from scratch. D is wrong because DATESMTD never silently expands into a year-to-date calculation. YTD behavior requires DATESYTD or TOTALYTD. The function is strictly month-bound based on the latest date it sees. Study tip: Whenever you see time-intelligence functions at a subtotal level, ask yourself: what is the last date in that filter context? The answer to that question determines exactly what range the function will use.

Question 4

A report lists each date in March as a separate row. The model has a valid marked date table, and the following measure is used:

Sales MTD = CALCULATE([Net Sales], DATESMTD('Date'[Date]))

For the row corresponding to March 10, [Net Sales] is evaluated after DATESMTD modifies the date context.

Which date range contributes to Sales MTD on the March 10 row?

  1. March 10 only, because the row initially filters the date table to that date
  2. March 1 through March 10, because the measure expands to the current month to date (correct answer)
  3. March 1 through March 31, because DATESMTD always returns the complete current month
  4. January 1 through March 10, because month-to-date inherits the current calendar year
Explanation: When working with time intelligence functions in DAX, the key question to ask is: what does this function do to the existing filter context? DATESMTD doesn't simply respect whatever date is currently filtered — it actively replaces the date filter with a new set of dates spanning from the first day of the current month through the latest visible date. For the March 10 row, the row context places a filter on March 10 in the Date table. When CALCULATE executes DATESMTD('Date'[Date]), it overrides that single-day filter and returns all dates from March 1 through March 10, inclusive. [Net Sales] is then evaluated over that expanded range, giving you a true month-to-date accumulation. That's why B is correct. A misunderstands how CALCULATE interacts with time intelligence. The single-day row filter is not preserved — DATESMTD explicitly replaces it. If you wanted only March 10's sales, you'd just use [Net Sales] directly without any time intelligence wrapper. C describes what DATESMTD would return if you evaluated it on March 31 — the last day of the month. Since the current date in context is March 10, the function stops there, not at month-end. Confusing "month to date" with "full month" is a classic trap. D describes year-to-date behavior (DATESYTD), not month-to-date. MTD resets at the beginning of each month, not the beginning of the year. A useful memory anchor: MTD = resets monthly, expands daily. Always pair the function name with its reset boundary — DATESMTD resets at month start, DATESYTD resets at year start.

Question 5

A model contains a marked date table named Date with an active relationship from Date[Date] to Sales[OrderDate]. The existing measure [Net Sales] sums sales after discounts. You need a measure that returns net sales from the beginning of the calendar year through the last date in the current filter context.

Which measure should you create?

  1. Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date])) (correct answer)
  2. Sales YTD = CALCULATE([Net Sales], DATESMTD('Date'[Date]))
  3. Sales YTD = CALCULATE([Net Sales], DATEADD('Date'[Date], -1, YEAR))
  4. Sales YTD = CALCULATE([Net Sales], LASTDATE('Date'[Date]))
Explanation: When you see a question asking for cumulative totals from the start of the calendar year to the current date, you're being tested on DAX time intelligence functions — specifically which function matches a year-to-date accumulation pattern. DATESYTD is purpose-built for exactly this scenario. It returns a table of dates from January 1st (or a custom fiscal year-end) through the last date in the current filter context. Wrapping it inside CALCULATE replaces the existing date filter with that range, so [Net Sales] accumulates correctly from the beginning of the year. This makes A the correct answer. B is a trap for students who confuse YTD with MTD. DATESMTD returns dates from the beginning of the current month, not the year — it would give you month-to-date totals, not year-to-date. C uses DATEADD with -1, YEAR, which shifts the entire filter context back by one full year. This is used for prior-year comparisons (like "same period last year"), not for accumulating from the start of the current year. D uses LASTDATE, which collapses the filter to a single date — the latest one in context. This would return net sales for only that one day, not a cumulative sum from the beginning of the year. A useful memory trick: the suffix in the function name tells you the accumulation period — YTD = Year, MTD = Month, QTD = Quarter. When the question says "beginning of the calendar year through today," match it directly to DATESYTD.

Question 6

A company has a fiscal year that ends on June 30. When a report is filtered to February 15, 2025, the fiscal YTD measure must include transactions from July 1, 2024, through February 15, 2025.

Which measure meets the requirement?

  1. Fiscal Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date], "6/30")) (correct answer)
  2. Fiscal Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date], "7/1"))
  3. Fiscal Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date]))
  4. Fiscal Sales YTD = CALCULATE([Net Sales], DATESMTD('Date'[Date], "6/30"))
Explanation: When working with fiscal year time intelligence in DAX, the key function to know is DATESYTD, which returns a year-to-date date range. By default, it assumes a calendar year ending December 31 — but it accepts an optional second argument: the end date of the fiscal year, formatted as a month/day string like "6/30". Answer A is correct because DATESYTD('Date'[Date], "6/30") tells DAX that the fiscal year ends on June 30. So when the report is filtered to February 15, 2025, DAX works backward from that end-date definition and correctly sets the YTD window from July 1, 2024 through February 15, 2025 — exactly what the requirement specifies. Answer B uses "7/1" as the year-end date, which would define a fiscal year ending July 1. This shifts the YTD window incorrectly, starting the new fiscal year on July 2 rather than July 1 — a subtle but meaningful off-by-one error that misaligns the date boundary. Answer C omits the second argument entirely, so DATESYTD defaults to a December 31 year-end. This would return January 1, 2025 through February 15, 2025 — completely ignoring the fiscal year structure. Answer D uses DATESMTD instead of DATESYTD. DATESMTD returns a month-to-date range, not year-to-date, so it would only capture February 1–15, 2025 regardless of any fiscal year argument. Study tip: Memorize that DATESYTD's optional second parameter is the last day of the fiscal year, not the first. Confusing "6/30" with "7/1" is a classic trap on this exam.

Question 7

A model's Date table was created by importing only dates found in weekday sales records. The table omits weekends, contains duplicate dates, and is related to a fact table that includes some weekend transactions. A YTD measure using DATESYTD('Date'[Date]) produces unreliable totals.

Which change most directly establishes the date-table structure required for reliable period-to-date calculations?

  1. Create a unique, contiguous calendar covering all required dates, mark it as the date table, and relate it to the fact table (correct answer)
  2. Remove duplicate dates, retain weekday-only rows, format the date column, and sort it in ascending order
  3. Keep the imported dates, enable bidirectional filtering, and place the fact-table date in every report slicer
  4. Convert each date to text, add month and year labels, and configure the labels to sort chronologically
Explanation: When you see a question about time intelligence functions like DATESYTD, TOTALYTD, or SAMEPERIODLASTYEAR, your first instinct should be to evaluate the date table itself — because Power BI's time intelligence engine has strict structural requirements that must be met before any calculation can be trusted. For time intelligence to work reliably, Power BI requires a date table that is contiguous (no gaps), contains unique dates (no duplicates), and is explicitly marked as the date table via the "Mark as date table" setting. This allows the DAX engine to understand the full calendar context when evaluating period boundaries. The scenario's table violates all three rules — it skips weekends, contains duplicates, and was never marked — so DATESYTD cannot correctly determine where a year starts or ends. Option A directly addresses every structural requirement: building a complete, gap-free calendar, ensuring uniqueness, marking it as the date table, and relating it to the fact table. This is the textbook fix. Option B is a partial improvement — removing duplicates and sorting help — but retaining weekday-only rows preserves the gaps, meaning period-to-date calculations will still miscalculate across missing days. Formatting alone doesn't satisfy Power BI's engine. Option C is a red herring. Bidirectional filtering affects how filters propagate between tables; it has nothing to do with the structural integrity of the date table or fixing YTD logic. Option D introduces text conversion, which actively breaks time intelligence — DAX time functions require an actual date data type, not text labels or strings. Study tip: Memorize the three pillars of a valid Power BI date table — contiguous, unique, marked. Any question describing broken time intelligence should send you straight to checking these three requirements first.

Question 8

The Sales table has OrderDate and ShipDate columns. Date[Date] has an active relationship to Sales[OrderDate] and an inactive relationship to Sales[ShipDate]. [Net Sales] is an existing measure. You must calculate YTD sales based on shipment date while retaining the current product and customer filters.

Which measure should you create?

  1. Ship Sales YTD = CALCULATE([Net Sales], DATESYTD('Date'[Date]))
  2. Ship Sales YTD = CALCULATE([Net Sales], DATESYTD(Sales[ShipDate]))
  3. Ship Sales YTD = CALCULATE([Net Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]), DATESYTD('Date'[Date])) (correct answer)
  4. Ship Sales YTD = CALCULATE([Net Sales], USERELATIONSHIP(Sales[OrderDate], 'Date'[Date]), DATESYTD('Date'[Date]))
Explanation: When working with inactive relationships in DAX, the key concept to understand is that Power BI only uses one relationship between two tables at a time. To temporarily activate an inactive relationship inside a calculation, you must explicitly invoke USERELATIONSHIP() as a filter modifier inside CALCULATE(). Here, your data model has an active relationship on OrderDate and an inactive one on ShipDate. To compute YTD sales based on shipment date, you need two things working together: activate the ShipDate relationship so date filtering flows through that column, and apply the DATESYTD time intelligence function using the Date table. Option C does exactly this — USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]) activates the inactive relationship, and DATESYTD('Date'[Date]) correctly uses the Date table's date column to define the year-to-date window. Both modifiers sit inside CALCULATE(), which applies them simultaneously while preserving existing product and customer filters from the report context. Option A is wrong because it never activates the ShipDate relationship, so DATESYTD still operates through the active OrderDate path — you'd get order-date YTD, not shipment-date YTD. Option B passes Sales[ShipDate] directly into DATESYTD, which is invalid — DATESYTD requires a column from a marked Date table, not a fact table column, so this would return an error or incorrect results. Option D activates the OrderDate relationship, which is already the active one and adds no value — it still ignores ShipDate. Study tip: Whenever a question involves an inactive relationship plus time intelligence, always pair USERELATIONSHIP() with a DATESYTD (or similar) referencing the Date table column — never the fact table's date column.

Question 9

A developer creates a calculated column in the Sales table by attempting to use DATESMTD. The developer expects the result to recalculate whenever a user changes a date slicer or product slicer. The model already contains [Net Sales] and a valid Date table.

Which implementation will provide the required interactive MTD result?

  1. Create a calculated table from DATESMTD and aggregate its rows independently of the sales measure
  2. Create the same expression as a calculated column and configure its default aggregation as Sum
  3. Create an MTD column in Power Query and refresh the dataset whenever report users change slicers
  4. Create Sales MTD = CALCULATE([Net Sales], DATESMTD('Date'[Date])) as a model measure (correct answer)
Explanation: When working with time intelligence in Power BI, the critical distinction to understand is the difference between static and dynamic calculations. Calculated columns and Power Query transformations are evaluated once — at data refresh time — and never respond to slicer selections. Measures, by contrast, are recalculated on every filter context change, which is exactly what interactive reports require. This is why D is correct. Writing Sales MTD = CALCULATE([Net Sales], DATESMTD('Date'[Date])) as a measure allows DAX to re-evaluate the expression every time a user adjusts a slicer. DATESMTD returns all dates from the start of the month through the current filter context date, and CALCULATE applies that date range to [Net Sales] dynamically. The result responds to any filter the user applies. A is wrong because creating a calculated table from DATESMTD produces a static snapshot of date values at refresh time. Aggregating those rows independently of slicers won't respond to user interaction in the way the question requires. B fails for a similar reason — calculated columns are row-by-row computations baked into the model at refresh. No matter what aggregation you set as default, a calculated column cannot respond to slicer changes because it has no dynamic filter context. C is the most fundamentally flawed option. Power Query runs before the model loads; it has no awareness of report-level slicers whatsoever. Refreshing the dataset doesn't help — slicers interact with the model, not the data source. The study tip to remember: if a question mentions interactive or slicer-driven behavior, the answer almost always involves a measure, never a calculated column or Power Query step.

Question 10

A report is filtered through May 10, 2025. The business uses standard calendar quarters. You need a measure that returns sales from April 1 through May 10 rather than sales for a rolling three-month interval.

Which DAX expression should you use?

  1. Sales QTD = CALCULATE([Net Sales], DATESQTD('Date'[Date])) (correct answer)
  2. Sales QTD = CALCULATE([Net Sales], DATESMTD('Date'[Date]))
  3. Sales QTD = CALCULATE([Net Sales], DATESYTD('Date'[Date]))
  4. Sales QTD = CALCULATE([Net Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
Explanation: When working with time-intelligence functions in DAX, the key distinction is understanding what date range each function defines. The question asks for sales from the start of the current quarter through the last filtered date — that's a quarter-to-date calculation, not a rolling window. Option A uses DATESQTD, which returns all dates from the first day of the current quarter up to the current context date. Since the report is filtered through May 10, 2025, and Q2 begins April 1, this gives you exactly April 1 – May 10. That's the correct behavior the scenario describes. Option B uses DATESMTD, which resets at the start of the month, not the quarter. It would return May 1 – May 10, missing all of April — too narrow a window. Option C uses DATESYTD, which resets at the start of the year (January 1). This would return January 1 – May 10, spanning two full quarters — far too broad. Option D uses DATESINPERIOD with a rolling -3 MONTH interval, which counts back exactly three months from May 10 to February 10. This is a rolling range, not a quarter-to-date range — precisely what the question tells you to avoid. This is the classic distractor for anyone who confuses "three-month period" with "quarter-to-date." A helpful memory anchor: the S in DATESQTD, DATESMTD, and DATESYTD stands for the period boundary that resets — Quarter, Month, and Year respectively. When you see "QTD" in the business requirement, map it directly to DATESQTD.