Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: Calculated column Calculated table Measure Visual calculation
Note
This function is discouraged for use in visual calculations as it likely returns meaningless results.
Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals.
This function is suited to pass as a filter to the CALCULATE function. Use it to filter an expression by standard date intervals such as days, months, quarters, or years.
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
Term | Definition |
---|---|
dates |
A date column. |
start_date |
A date expression. |
number_of_intervals |
An integer that specifies the number of intervals to add to, or subtract from, the dates. |
interval |
The interval by which to shift the dates. The value for interval can be one of the following: DAY , MONTH , QUARTER , and YEAR |
A table containing a single column of date values.
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.
Time intelligence functions (DAX) Date and time functions (DAX) DATESBETWEEN function (DAX)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today