DATESINPERIOD

DATESINPERIOD is a DAX function that returns the dates from a given period.

Syntax

DATESINPERIOD(
   Dates,
   StartDate,
   NumberOfIntervals,
   Interval
)

Argument	Properties	Description
Dates		The name of a column containing dates or a one column table containing dates.
StartDate		Start date.
NumberOfIntervals		The number of intervals.
Interval		One of: Day, Month, Quarter, Year.

Return Values

A table containing a single column of date values.

Remarks

• In the most common use case, dates is a reference to the date column of a marked date table.

• If the number specified for number_of_intervals is positive, dates are moved forward in time; if the number is negative, dates are shifted backward in time.

• The interval parameter is an enumeration. Valid values are DAY, MONTH, QUARTER, and YEAR. Because it’s an enumeration, values aren’t passed in as strings. So don’t enclose them within quotation marks.

• The returned table can only contain dates stored in the dates column. So, for example, if the dates column starts from July 1, 2017, and the start_date value is July 1, 2016, the returned table will start from July 1, 2017.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

The following Sales table measure definition uses the DATESINPERIOD function to calculate revenue for the prior year (PY).

Notice the formula uses the MAX function. This function returns the latest date that’s in the filter context. So, the DATESINPERIOD function returns a table of dates beginning from the latest date for the last year.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

Revenue PY =
CALCULATE(
    SUM(Sales[Sales Amount]),
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -1,
        YEAR
    )
)

Consider that the report is filtered by the month of June 2020. The MAX function returns June 30, 2020. The DATESINPERIOD function then returns a date range from July 1, 2019 until June 30, 2020. It’s a year of date values starting from June 30, 2020 for the last year.

Related functions

Other functions related to DATESINPERIOD are:

• DATESBETWEEN

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax