nerdexam
Microsoft

70-467 · Question #113

You need to implement the SalesCommission model to support the planned reports and queries. What should you do?

The correct answer is C. Create a date table that contains a row for every date since data started being recorded. DAX time intelligence functions require a marked date table with a contiguous, uninterrupted row for every calendar date across the full data range, regardless of whether a transaction occurred on that date.

Design BI data models

Question

You need to implement the SalesCommission model to support the planned reports and queries. What should you do?

Options

  • ACreate a date table that contains only one row for each date on which a sale is recorded.
  • BUse the existing transaction date column in the sales table for date calculations.
  • CCreate a date table that contains a row for every date since data started being recorded.
  • DCreate a new calculated date column in the sales table for date calculations.

How the community answered

(16 responses)
  • A
    6% (1)
  • B
    13% (2)
  • C
    81% (13)

Why each option

DAX time intelligence functions require a marked date table with a contiguous, uninterrupted row for every calendar date across the full data range, regardless of whether a transaction occurred on that date.

ACreate a date table that contains only one row for each date on which a sale is recorded.

A date table containing only transaction dates introduces gaps in the sequence, causing time intelligence functions to miscount periods and return inaccurate year-to-date or prior-period comparison results.

BUse the existing transaction date column in the sales table for date calculations.

Using the transaction date column from the sales table directly cannot be marked as the model's date table and does not provide the contiguous date range required for DAX time intelligence functions to work correctly.

CCreate a date table that contains a row for every date since data started being recorded.Correct

A date table with a row for every date since recording began guarantees the continuous date sequence that functions like TOTALYTD, DATEADD, and SAMEPERIODLASTYEAR depend on. Without gaps, these functions can accurately calculate period-over-period comparisons and cumulative totals even for dates with no sales activity.

DCreate a new calculated date column in the sales table for date calculations.

A calculated date column inside the sales table is still bound to the transaction grain and cannot serve as a standalone, contiguous date dimension - it cannot be marked as a date table and does not support time intelligence.

Concept tested: Contiguous date table requirement for DAX time intelligence

Source: https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Topics

#tabular model#date table#time intelligence#DAX

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice