Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is working on a Power BI model for a manufacturing company. The model includes a 'Production' table with 'ProductID', 'ProductionDate', and 'UnitsProduced'. The analyst needs to create a measure that calculates the moving average of units produced over the last 7 days. Which DAX pattern should the analyst use?
- ASTARTOFMONTH
- BALL + FILTER
- CDATESINPERIOD
- DRELATEDTABLE
Show answer & explanationAnswer & explanation
Correct answer: C. DATESINPERIOD
DATESINPERIOD is a time intelligence function that returns a table containing a column of dates that begins with a given start date and continues for a specified number of intervals, which is perfect for defining a rolling window for a moving average.
Why the other options are wrong
- A. STARTOFMONTH returns the first date of the month, not a rolling 7-day period.
- B. ALL + FILTER is used to modify or remove filters, not specifically for defining date ranges for time intelligence.
- D. RELATEDTABLE is used to return a table of rows related to the current row, not for time intelligence calculations.
DATESINPERIOD Function
DATESINPERIOD is a DAX time intelligence function that returns a table that contains a column of dates starting with a given start date and continuing for the specified number of intervals.
- Used for defining dynamic date ranges.
- Essential for rolling calculations like moving averages.
- Requires a start date, number of intervals, and interval type (e.g., DAY, MONTH, YEAR).
- Returns a table of dates that can be used within CALCULATE.
Memory trick: Time intelligence functions are like a calendar, helping you navigate through dates.