Microsoft
70-467 · Question #167
70-467 Question #167: Real Exam Question with Answer & Explanation
The correct answer is B: Option B. YTD MDX calculations require the right aggregate function and period reference to adapt across multiple hierarchy levels without hardcoding a single level.
Question
You are creating a calculated measure in a sales cube by using a Multidimensional Expressions (MDX) calculation. The cube includes a date dimension. The date dimension has a hierarchy named Calendar that includes Calendar Year, Month, and Day attributes. The calculated measure must calculate the year-to-date sales for the current slicer. The MDX calculation must work for as many hierarchy levels as possible. You need to create a calculated measure for the cube that meets the requirements. Which expression should you use in the calculation designer? (More than one answer choice may achieve the goal. Select the BEST answer.)
Exhibit
Options
- AOption A
- BOption B
- COption C
- DOption D
Explanation
YTD MDX calculations require the right aggregate function and period reference to adapt across multiple hierarchy levels without hardcoding a single level.
Common mistakes.
- A. Option A likely uses a hardcoded or less flexible approach such as YTD() without a proper hierarchy reference, which may not resolve correctly at all levels of the Calendar hierarchy.
- C. Option C likely omits the Aggregate wrapper or uses an incorrect period anchor, causing it to fail or return wrong results when evaluated at hierarchy levels other than the one explicitly referenced.
- D. Option D likely uses a Sum or a fixed date range that does not adapt to the current slicer context, so it would fail to produce accurate YTD results at multiple hierarchy levels.
Concept tested. MDX year-to-date calculation across hierarchy levels
Reference. https://learn.microsoft.com/en-us/sql/mdx/periodstodate-mdx
Community Discussion
No community discussion yet for this question.