Microsoft Certified: Power BI Data Analyst AssociateVisualize and analyze the dataHard
A manufacturing company uses Power BI to monitor production efficiency. They have a dataset with 'Production Line', 'Product Type', 'Production Date', and 'Units Produced'. The operations manager wants to create a visual that shows, for each 'Production Line', the 'Units Produced' for the current month and the 'Units Produced' for the previous month, side-by-side, to quickly assess month-over-month performance. Which DAX function is essential for calculating the 'Units Produced Previous Month' for this comparison?
- ASUMX
- BRELATEDTABLE
- CCALCULATE with DATEADD
- DVALUES
Show answer & explanationAnswer & explanation
Correct answer: C. CALCULATE with DATEADD
To calculate 'Units Produced Previous Month', you need to shift the context of the date. The `CALCULATE` function allows you to modify the filter context, and `DATEADD` is a time intelligence function that shifts a set of dates by a specified interval. Combining them allows you to calculate the sum of units for the previous month.
Why the other options are wrong
- A. SUMX iterates over a table and sums an expression, which is good for row-by-row calculations but not for shifting time context.
- B. RELATEDTABLE returns a table of related rows, used in row context, not for time intelligence calculations.
- D. VALUES returns a table of unique values from a column, typically used for filtering or iterating, not for previous period calculations.
DATEADD DAX Function
A time intelligence function in DAX that returns a table that contains a column of dates, shifted either forward or backward in time by a specified number of intervals from the current context.
- Used within CALCULATE for time-shifted calculations.
- Requires a date column, number of intervals, and interval type (DAY, MONTH, QUARTER, YEAR).
- Essential for 'previous period' or 'next period' comparisons.
Memory trick: Calculate Dates, Shift Time