DATESINPERIOD DAX Function in Power BI
The DATESINPERIOD DAX function returns a table that contains a single column of dates that begins with a given start date and continues for the specified number of 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.
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.
DAX Syntax DATESINPERIOD(dates, start_date, number_of_intervals, interval)
The function has the following parameters:
- dates: The parameter dates is a date column.
- start_date: A date expression.
- number_of_intervals: 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.
- interval: The interval by which to shift the dates. The value for interval can be one of the following: DAY, MONTH, QUARTER, and YEAR
Example: The following sample formula creates a measure that calculates the next year income.
Let’s we have a following table named Sheet1 which has Dates column of Date data type as shown in the image below:
Let’s create a calculated table, with the following DAX expression.
DAX
The output of the above dax code is shown below:
DAX
The output of the above dax code is shown below:
DAX
The output of the above dax code is shown below:
