Which DAX function creates the rolling date range for the last N months in the measure?

Which DAX function creates the rolling date range for the last N months in the measure?

• A. CALCULATE
• B. VALUES
• C. DATESINPERIOD
• D. FILTER

Answer: (C)

DatesINPERIOD builds a rolling date window. It takes a date column, a reference date to anchor the period (usually the maximum date in the current context), how many intervals to include, and the interval type (MONTH). By using a negative number of months, you specify a window that goes back from the anchor date, giving you the last N months. When you wrap this in CALCULATE, the measure is evaluated over that specific date range, effectively producing a rolling window. Example: CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) computes total sales over the last 12 months ending at the latest date in context. Other options don’t inherently define a rolling date range. CALCULATE modifies filter context but doesn’t itself generate the date span. VALUES returns distinct dates but not a contiguous rolling range. FILTER can constrain rows but would require additional logic to build the date window, making DATESINPERIOD the most straightforward tool for this pattern.

DatesINPERIOD builds a rolling date window. It takes a date column, a reference date to anchor the period (usually the maximum date in the current context), how many intervals to include, and the interval type (MONTH). By using a negative number of months, you specify a window that goes back from the anchor date, giving you the last N months. When you wrap this in CALCULATE, the measure is evaluated over that specific date range, effectively producing a rolling window.

Example: CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) computes total sales over the last 12 months ending at the latest date in context.

Other options don’t inherently define a rolling date range. CALCULATE modifies filter context but doesn’t itself generate the date span. VALUES returns distinct dates but not a contiguous rolling range. FILTER can constrain rows but would require additional logic to build the date window, making DATESINPERIOD the most straightforward tool for this pattern.