Microsoft Power BI Quiz: Rolling Calculations
10 questions · exam conditions
0:00
Rolling CalculationsQuestion 1 of 10

A report has a date slicer set from January through June. Users want the June value of [Rolling 12M Sales] to include sales from the previous July through the current June, even though the slicer hides the previous year's dates. Product and region slicers must still affect the result.

Which pattern should the rolling measure use after capturing EndDate from the current context?

CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
CALCULATE([Sales Amount], REMOVEFILTERS(Sales), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
CALCULATE([Sales Amount], REMOVEFILTERS('Date'), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
CALCULATE([Sales Amount], ALLSELECTED('Date'), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Rolling Calculations

Practice Rolling Calculations 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 Rolling Calculations, 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 has a date slicer set from January through June. Users want the June value of [Rolling 12M Sales] to include sales from the previous July through the current June, even though the slicer hides the previous year's dates. Product and region slicers must still affect the result.

Which pattern should the rolling measure use after capturing EndDate from the current context?

  1. CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
  2. CALCULATE([Sales Amount], REMOVEFILTERS(Sales), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
  3. CALCULATE([Sales Amount], REMOVEFILTERS('Date'), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH)) (correct answer)
  4. CALCULATE([Sales Amount], ALLSELECTED('Date'), DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
Explanation: When building rolling window measures in DAX, the central challenge is controlling which filters get overridden and which must be preserved. Ask yourself: what needs to be freed (the date slicer), and what needs to stay locked (product, region)? The winning pattern is C because REMOVEFILTERS('Date') surgically removes only the date table's filter context — specifically the slicer restricting visibility to January–June — then DATESINPERIOD rebuilds exactly the 12-month window ending at EndDate. Product and region filters live on their own tables and are never touched, so those slicers continue working correctly. This is precisely the behavior the business requirement demands. A fails silently. Without removing the date slicer first, DATESINPERIOD tries to evaluate July–December of the prior year, but those dates are already filtered out by the slicer. The function returns only whatever dates survive the existing filter — your "12-month" window is silently truncated to 6 months or fewer. B uses REMOVEFILTERS(Sales) instead of REMOVEFILTERS('Date'). Removing filters from the fact table doesn't free the date dimension filter, so the slicer still blocks prior-year dates. Worse, it could inadvertently wipe out product or region filter propagation that flows through the Sales table. D applies ALLSELECTED('Date'), which restores the user's visible selection on the date table — meaning it still respects the January–June slicer boundary. This is the same fundamental problem as A: prior-year months remain invisible. Study tip: Memorize this rule — to override a slicer on a dimension table without disturbing other slicers, use REMOVEFILTERS(DimensionTable), never ALLSELECTED.

Question 2

A marked date table contains every calendar date. The business requests trailing thirty-day average sales per calendar day, including weekends and other dates with no sales as zero. The fact table contains rows only on dates when sales occurred.

Which DAX design satisfies the requirement?

  1. Use AVERAGEX over the thirty-day calendar-date set and evaluate COALESCE([Sales Amount], 0) for each date. (correct answer)
  2. Use AVERAGE(Sales[Amount]) after applying a thirty-day filter to the sales fact table.
  3. Use AVERAGEX over VALUES(Sales[OrderDate]) and evaluate [Sales Amount] for each fact date.
  4. Use the thirty-day sales total divided by the count of distinct order dates in the fact table.
Explanation: When calculating an average that must treat missing data as zero, the key question is: what is the denominator? The business wants total sales divided by 30 calendar days — not just the days when sales happened. This distinction determines which DAX pattern is correct. Option A satisfies the requirement precisely. By iterating AVERAGEX over the full thirty-day set of calendar dates (from the marked date table) and using COALESCE([Sales Amount], 0) for each row, you ensure every calendar day contributes to the average — including weekends and holidays where no sales occurred. The denominator is always 30, which matches the business definition. Option B fails because AVERAGE(Sales[Amount]) computes the mean of existing rows in the fact table. It never considers dates with zero transactions, so the denominator is the count of sales rows, not calendar days — producing an inflated average. Option C makes a similar mistake. Iterating over VALUES(Sales[OrderDate]) gives you only dates that actually appear in the fact table. Days with no sales are completely excluded, so you're averaging across selling days only, not all thirty calendar days. Option D divides by the count of distinct order dates, which again excludes zero-sales days from the denominator. Even if the numerator (total sales) is correct, the denominator is wrong, and the result overstates the daily average. A useful study pattern: whenever a Power BI question mentions "including days with no activity" or "treat missing as zero," immediately think AVERAGEX over a calendar table — never over the fact table — and use COALESCE or IF to handle blanks explicitly.

Question 3

A company wants a trailing six-month average of monthly sales totals. Months with no sales must contribute zero to the average. The marked 'Date' table contains a unique [MonthStart] value for every calendar month.

Which approach produces the required measure without averaging individual transactions or excluding months with no sales?

  1. Iterate over sales rows in the six-month window by using AVERAGEX(Sales, Sales[Amount]).
  2. Divide the six-month sales total by six, regardless of how many calendar months are available.
  3. Build the six-month set of calendar months, then use AVERAGEX over that set with COALESCE([Sales Amount], 0). (correct answer)
  4. Apply AVERAGE(Sales[Amount]) after filtering the date table to the trailing six-month window.
Explanation: When designing a trailing-average measure in DAX, the core challenge is controlling what gets averaged — individual transactions, or calendar months? This question tests whether you understand how to build a denominator-aware average that respects empty periods. The right approach, answer C, works by first constructing the full set of six calendar months from the marked Date table, then iterating over those months with AVERAGEX. Using COALESCE([Sales Amount], 0) ensures that months with no transactions contribute zero rather than being silently dropped. The result is always divided by exactly six month-rows, honoring the business requirement of a true trailing-six-month average. Answer A fails because AVERAGEX(Sales, Sales[Amount]) iterates over transaction rows, not months. If one month has 500 transactions and another has none, the denominator reflects row counts, not calendar months — producing a distorted average that ignores the zero-sales months entirely. Answer B gets the denominator right (always dividing by six) but hardcodes it, making the measure fragile. At the start of a dataset where fewer than six months of history exist, you'd still divide by six, incorrectly understating the average for early periods. Answer D uses AVERAGE(Sales[Amount]), which averages individual transaction amounts after a date filter — the same row-level problem as A. Filtering the Date table doesn't change the fact that AVERAGE aggregates over the wrong granularity. Study tip: On Power BI DAX questions, whenever a requirement mentions calendar periods with possible gaps, reach for AVERAGEX over a date-table set with COALESCE(..., 0) — it gives you explicit control over both the numerator and the denominator.

Question 4

A customer can place orders in several months. The business requires the number of unique customers who purchased at least once during each trailing three-month window.

Which method returns the correct rolling distinct-customer count?

  1. Count sales rows in the trailing three-month window and divide by the number of included months.
  2. Calculate each month's distinct-customer count and sum the three monthly results by using SUMX.
  3. Calculate each month's distinct-customer count and average the three monthly results by using AVERAGEX.
  4. Calculate DISTINCTCOUNT(Sales[CustomerID]) once after filtering the date table to the full trailing three-month window. (correct answer)
Explanation: Whenever you see a question about distinct counts across a time window in Power BI, stop and ask yourself: can distinct counts be combined arithmetically? The answer is almost always no — and that insight separates the correct approach from every trap here. The business wants to know how many unique customers appear anywhere within a rolling three-month window. A customer who buys in all three months should still be counted once. The only correct way to achieve this is option D: apply a single date filter spanning the full three-month window and then run DISTINCTCOUNT(Sales[CustomerID]) against that filtered context. This naturally deduplicates customers across all three months in one pass, producing exactly the count the business needs. Option B fails because summing three separate monthly distinct counts double- (or triple-) counts any customer who purchased in more than one of those months. If a customer appears in all three months, they contribute 3 to the sum instead of 1. Option C makes the same fundamental error — averaging inflated, overlapping counts still yields a mathematically meaningless result for distinct customers. Option A is the most misleading distractor: dividing a row count by the number of months gives you an average transaction figure, which has nothing to do with unique customers at any time horizon. Study tip: Whenever a question involves distinct counts over a multi-period window, remember that distinct counts are not additive. You must filter the entire window first, then count — never aggregate pre-computed distinct counts with SUMX, AVERAGEX, or division.

Question 5

A monthly report must display a trailing twelve-month average only when all twelve calendar months exist in the model's date table. It must return blank during the first eleven months of the date table. A calendar month with no sales still counts as an available month.

Which validation should the measure perform before returning the rolling result?

  1. Count distinct month values in the rolling window from the date table, and return the result only when the count is twelve. (correct answer)
  2. Count distinct order months in the sales table, and return the result only when the count is twelve.
  3. Count sales rows in the rolling window, and return the result only when at least twelve rows exist.
  4. Test whether the rolling sales total is nonblank, and return the result whenever any sales are present.
Explanation: When building a trailing twelve-month (TTM) measure in Power BI, you need to ask yourself: what exactly qualifies as "twelve complete months"? The passage is explicit — a month with zero sales still counts, so your validation must check the date table, not the sales table. This is why A is correct. By counting distinct month values within the rolling window from the date table, you capture every calendar month regardless of whether any transactions occurred. When that count reaches twelve, you know a full year of history exists in the model, and the measure can safely return the rolling average. This is the most reliable gate because the date table is purpose-built to be complete and contiguous. B fails because it counts distinct months from the sales table. If a month has no sales, it won't appear there — meaning a quiet month is invisible to the count. You could easily satisfy "twelve" only after far more than twelve calendar months have passed, or skip the threshold entirely in low-activity periods. C compounds the same mistake by counting rows, not months. A single busy month could contain thousands of rows, while twelve slow months might contain fewer than twelve rows total. Row count has no meaningful relationship to the number of elapsed calendar months. D tests whether rolling sales are nonblank, which completely ignores the twelve-month requirement. Any single sale would trigger the result, defeating the entire purpose of the trailing window guard. Study tip: On Power BI exam questions involving time-intelligence completeness checks, always anchor your validation to the date table — it's the authoritative, gap-free calendar that sales data can never replicate.

Question 6

A report is refreshed daily and includes the current, incomplete month. For every date context, a measure must return sales for the twelve completed calendar months immediately preceding the current month. The current partial month must never be included.

Which DAX pattern establishes the correct rolling window?

  1. VAR EndDate = MAX('Date'[Date]) RETURN CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], EndDate, -12, MONTH))
  2. VAR EndDate = EOMONTH(MAX('Date'[Date]), -1) RETURN CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], EndDate, -12, MONTH)) (correct answer)
  3. VAR EndDate = EOMONTH(MAX('Date'[Date]), 0) RETURN CALCULATE([Sales Amount], DATESYTD('Date'[Date]))
  4. VAR EndDate = EOMONTH(MAX('Date'[Date]), -12) RETURN CALCULATE([Sales Amount], DATESMTD('Date'[Date]))
Explanation: When building a rolling 12-month measure that excludes the current partial month, you need to anchor your end date to the last completed month, not the latest date in context. The key function here is EOMONTH, which returns the last day of a month offset by a specified number of months. Option B correctly sets EndDate = EOMONTH(MAX('Date'[Date]), -1), which gives you the final day of the previous month — guaranteeing the current, incomplete month is excluded. DATESINPERIOD then walks back exactly 12 months from that anchor, delivering a clean window of 12 fully completed calendar months. Option A is the most tempting trap. It uses MAX('Date'[Date]) as the end date, which lands somewhere inside the current partial month. DATESINPERIOD will then include whatever days have elapsed in the current month, violating the "completed months only" requirement. Option C pairs EOMONTH(..., 0) — the end of the current month — with DATESYTD, which always resets to January 1st of the current year. This has nothing to do with a 12-month rolling window; it would return a year-to-date total instead. Option D calculates EOMONTH(..., -12), pushing the anchor back a full year rather than one month, then combines it with DATESMTD, which returns only the current month-to-date. Both the anchor and the time function are wrong for this scenario. The study tip to remember: whenever a question specifies "completed months only," your first instinct should be EOMONTH(..., -1) to safely exclude any partial current month before applying your rolling window logic.

Question 7

A date table extends through the end of next year, but sales data is loaded only through the latest completed business day. A card must show trailing thirty-day sales ending on the latest date that has sales for the current product selection. There is no date slicer.

Which expression should be used to determine the rolling window's ending date?

  1. TODAY(), returning the current system date even when that date has no loaded sales data.
  2. MAX('Date'[Date]), returning the final future date present in the calendar table.
  3. MAXX(FILTER(ALL('Date'), NOT ISBLANK(CALCULATE([Sales Amount]))), 'Date'[Date]), scanning calendar dates while preserving the current product filter context. (correct answer)
  4. MAXX(ALL(Sales), Sales[OrderDate]), returning the globally latest order date after removing all filters from the fact table.
Explanation: When building a dynamic rolling window in DAX without a date slicer, your central challenge is finding the last date that actually has data — not just the last date in your calendar or on today's clock — while still respecting whatever product filter the user has applied. This distinction drives everything. Option C achieves exactly this. MAXX(FILTER(ALL('Date'), NOT ISBLANK(CALCULATE([Sales Amount]))), 'Date'[Date]) walks through every calendar date (using ALL to bypass any date filters), tests whether [Sales Amount] is non-blank for each one within the current product filter context, and returns the maximum qualifying date. Because CALCULATE inside FILTER inherits the outer row context converted to filter context, the product selection remains active. The result is the latest date that genuinely has sales for the selected product — exactly what the card needs. Option A fails because TODAY() returns the system date regardless of whether any sales data exists for that day. If today is a weekend or a holiday, your trailing-30-day window is anchored to a date with no data, producing misleading results. Option B fails because MAX('Date'[Date]) simply returns the furthest date in your calendar table — a future date well beyond any loaded sales — so your window would span a period that is mostly empty. Option D is closer in spirit but wrong in scope. MAXX(ALL(Sales), Sales[OrderDate]) strips all filters from the fact table, including the product filter, so it returns the global latest order date across every product, ignoring the current selection entirely. Your study tip: whenever a DAX measure must be sensitive to both data existence and current filter context, reach for FILTER combined with CALCULATE and NOT ISBLANK — this pattern is a reliable way to probe which rows actually have values under the active filter.

Question 8

A sales model contains a marked date table named 'Date' with an active one-to-many relationship to Sales. The measure [Sales Amount] sums sales revenue. A report displays one row per calendar month.

You need a measure that, for each displayed month, returns sales for the trailing twelve months ending on the last date of that month. The measure must continue to respect product and region filters. Which DAX expression should you use?

  1. CALCULATE([Sales Amount], DATESYTD('Date'[Date]))
  2. VAR EndDate = MAX('Date'[Date]) RETURN CALCULATE([Sales Amount], DATESINPERIOD('Date'[Date], EndDate, -12, MONTH)) (correct answer)
  3. VAR EndDate = MAX('Date'[Date]) RETURN CALCULATE([Sales Amount], DATEADD('Date'[Date], -12, MONTH))
  4. VAR EndDate = MAX('Date'[Date]) RETURN CALCULATE([Sales Amount], FILTER(ALL(Sales), Sales[OrderDate] <= EndDate))
Explanation: When you need a rolling time window in DAX, the key question to ask is: "Does this function return a dynamic, sliding date range, or a fixed one anchored to a calendar boundary?" Trailing-twelve-month (TTM) calculations require a sliding window that ends wherever the current filter context ends. Option B is correct because DATESINPERIOD is purpose-built for exactly this pattern. It takes a start anchor (EndDate = MAX('Date'[Date])), steps back 12 months, and returns every date in that window. Wrapping it in CALCULATE replaces the date filter while leaving product and region filters intact — precisely what the question requires. Option A uses DATESYTD, which resets at January 1st each year. That gives you year-to-date totals, not a trailing twelve-month window — a very common trap on Power BI questions. Option C uses DATEADD, which shifts the entire existing date filter back by 12 months rather than constructing a 12-month range ending today. For a single month in context, it returns that same month from the prior year — useful for year-over-year comparisons, but wrong here. Option D bypasses the date table entirely by filtering Sales directly with ALL(Sales). This breaks the relationship-based filter model, ignores the marked date table, and — critically — removes product and region filters by calling ALL(Sales), violating the stated requirement. Study tip: Memorize the distinction between DATESINPERIOD (sliding window, you control the anchor and length) and DATEADD (shift, same duration). On the Power BI exam, TTM questions almost always point to DATESINPERIOD.

Question 9

A model has separate 'Date', Product, and Region dimensions. A trailing ninety-day sales measure is correct by date but displays the same value for every product. The measure currently uses FILTER(ALL(Sales), ...) to define the date window.

How should you revise the measure so that it removes the current date restriction while preserving product and region context?

  1. Use REMOVEFILTERS(Product, Region) before applying the trailing date window from the date table.
  2. Replace ALL(Sales) with ALL(Product), and then apply the trailing date window to the sales table.
  3. Keep ALL(Sales) and reapply only the current product by using SELECTEDVALUE(Product[ProductKey]).
  4. Replace ALL(Sales) with REMOVEFILTERS('Date'), and then apply the trailing date window from the date table. (correct answer)
Explanation: When writing trailing period measures in DAX, you need to think carefully about which filters you remove and which you preserve. The goal here is to clear only the date filter so you can redefine the date window yourself, while letting product and region filters from the report context continue to do their job. REMOVEFILTERS('Date') does exactly that — it strips only the date table's filter, leaving the Product and Region filters untouched. Once the date restriction is cleared, you apply your own trailing 90-day window using DATESINPERIOD or a similar function against the date table. This is why D is correct: it surgically removes only what needs removing and rebuilds the date context intentionally. A goes in the opposite direction — removing Product and Region filters is precisely what causes the symptom you're trying to fix (every product showing the same value). Stripping those context filters would make the measure context-blind. B replaces ALL(Sales) with ALL(Product), which clears product filters but doesn't address the date dimension at all. Your date window would still be controlled by whatever date filter the visual applies, not by your 90-day logic — and product context would be broken. C tries to patch the wrong root cause. Using SELECTEDVALUE(Product[ProductKey]) to reapply a single product key is fragile, fails on multi-select scenarios, and still doesn't fix the date filter architecture that's at the core of the problem. A useful pattern to remember: when a trailing-period measure ignores a dimension, you likely used ALL too broadly. Scope your REMOVEFILTERS to only the table or column whose filter you need to redefine.

Question 10

A matrix displays calendar months and a trailing three-month sales measure. The grand total should represent one trailing three-month window ending on the latest date in the total's filter context. It should not add together the overlapping rolling values shown for individual month rows.

Which design best produces the required grand total?

  1. Use the same context-sensitive rolling measure at every level and let the total recalculate it using the total's maximum date. (correct answer)
  2. Use SUMX over visible months so that the total equals the sum of all displayed rolling values.
  3. Remove all date filters at the total level and return total sales for the complete history of the model.
  4. Average the displayed monthly rolling values so that overlapping months have less effect on the result.
Explanation: When building rolling window measures in Power BI, the critical question is: what filter context does each cell see? A trailing three-month measure typically uses CALCULATE with date intelligence functions to define its own window relative to the maximum date in context. This behavior is exactly what makes option A correct — at the grand total level, the filter context contains all months, so the measure's maximum date becomes the latest date in the entire selection, and the measure correctly computes one single trailing three-month window ending there. The total recalculates from scratch rather than aggregating row values, which is precisely what the requirement demands. Option B is the classic trap. Using SUMX over visible months forces the total to literally add each row's rolling value together. Since those rolling windows overlap (month 3's window shares data with month 2's window), you double- or triple-count sales, producing a meaninglessly inflated total. Option C removes all date filters, which would return lifetime sales for the entire model — a completely different figure unrelated to any trailing window. This misunderstands how REMOVEFILTERS or ALL behaves and ignores the requirement entirely. Option D averages the displayed rolling values, which softens the double-counting problem but doesn't eliminate it. An average of overlapping windows is still not a single coherent trailing three-month total — it's a statistical workaround that doesn't match the business requirement. The study tip to remember: whenever a measure is context-sensitive by design, totals automatically get the right behavior for free — trust the recalculation, and never aggregate rolling or ratio measures with SUMX across rows.