nerdexam
Microsoft

70-466 · Question #117

You are developing a SQL Server Analysis Services (SSAS) tabular project. You need to define a measure named Profit and include a table named Date. The table includes year, semester, quarter, month…

The correct answer is A. DEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])) C. Use the Business Intelligence Wizard and then use the Define time intelligence enhancement. D. DEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], PARALLELPERIOD('Date'[Date], -12, MONTH)). Year-over-year growth in DAX can be computed using SAMEPERIODLASTYEAR, PARALLELPERIOD with -12 months, or via the Business Intelligence Wizard time intelligence feature - each correctly shifts the date context back one full year.

Build a tabular data model

Question

You are developing a SQL Server Analysis Services (SSAS) tabular project. You need to define a measure named Profit and include a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates. You need to create a measure to report on year-over-year growth of profit. What should you do? (Each answer presents a complete solution. Choose all that apply.)

Options

  • ADEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
  • BDEFINE the following calculation. Year Over Year Revenue Growth:=CALCULATE([Revenue], DATEADD('Date'[Date], 1, YEAR))
  • CUse the Business Intelligence Wizard and then use the Define time intelligence enhancement.
  • DDEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], PARALLELPERIOD('Date'[Date], -12, MONTH))

How the community answered

(44 responses)
  • A
    66% (29)
  • B
    34% (15)

Why each option

Year-over-year growth in DAX can be computed using SAMEPERIODLASTYEAR, PARALLELPERIOD with -12 months, or via the Business Intelligence Wizard time intelligence feature - each correctly shifts the date context back one full year.

ADEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))Correct

SAMEPERIODLASTYEAR returns the set of dates from the same period in the previous year, so subtracting CALCULATE([Revenue], SAMEPERIODLASTYEAR(...)) from current [Revenue] correctly computes year-over-year growth.

BDEFINE the following calculation. Year Over Year Revenue Growth:=CALCULATE([Revenue], DATEADD('Date'[Date], 1, YEAR))

DATEADD with +1 YEAR shifts the date context one year into the future rather than one year into the past, producing the wrong comparison period for year-over-year growth, and the formula also omits the subtraction from current [Revenue].

CUse the Business Intelligence Wizard and then use the Define time intelligence enhancement.Correct

The Business Intelligence Wizard's 'Define time intelligence' enhancement generates standard time comparison measures including year-over-year calculations automatically, using the Date table configuration.

DDEFINE the following calculation. Year Over Year Revenue Growth:=[Revenue] - CALCULATE([Revenue], PARALLELPERIOD('Date'[Date], -12, MONTH))Correct

PARALLELPERIOD('Date'[Date], -12, MONTH) shifts the current date context back 12 months, which is equivalent to the same period last year, making the subtraction from current [Revenue] a valid year-over-year growth formula.

Concept tested: DAX time intelligence functions for year-over-year growth

Source: https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Topics

#DAX time intelligence#SAMEPERIODLASTYEAR#PARALLELPERIOD#year-over-year growth

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice