DatesInPeriod Vs DatesBetween – Power BI DAX

In this post we will discuss about the DatesInPeriod and DatesBetween DAX functions. In Power BI there are many time intelligence functions and each of those is helping in aspects of analyzing data on dates. These two above functions work very similar to each other but have a different usage, let us explore it.

Let us take one sample data, which is the calculated date table using the calendar function as below,

Calendar = CALENDAR(DATE(2006, 1, 1), DATE(2008, 12, 31))

This will generate the unique dates from 1^st Jan 2006 to 31^st Dec 2008

DatesInPeriod:
Syntax: DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

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.

Description of parameters

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

DatesBetween:

Syntax: DATESBETWEEN(<dates>, <start_date>, <end_date>)

Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.

This function is suited to pass as a filter to the CALCULATE function. Use it to filter an expression by a custom date range.

Description of parameters
Dates	A date column.
start_date	A date expression.
end_date	A date expression.

Understanding the DatesInPeriod:

Bring the date from calendar table into the table visual of Power BI visual along with measures mentioned below.

First Date DatesInPeriod =
FIRSTDATE(
DATESINPERIOD(‘Calendar'[Date],
LASTDATE( ‘Calendar'[Date]),-1,YEAR)
)

Last Date DatesInPeriod =
LASTDATE(
DATESINPERIOD(‘Calendar'[Date],
LASTDATE( ‘Calendar'[Date]),-1,YEAR)
)

We see the following table in visual,

Understanding the DatesBetween:-

Bring the date from calendar table into the table visual of Power BI visual along with measures mentioned below.

First Date DatesBetween =
FIRSTDATE(
DATESBETWEEN(
‘Calendar'[Date],
DATEADD(LASTDATE(‘Calendar'[Date]),-1,YEAR)
,LASTDATE(‘Calendar'[Date])
)
)

Last Date DatesBetween =

LASTDATE(
DATESBETWEEN(
‘Calendar'[Date],
DATEADD(LASTDATE(‘Calendar'[Date]),-1,YEAR)
,LASTDATE(‘Calendar'[Date]
)
)

Inference:

1. In both cases I.e., DatesInPeriod and DatesBetween we are going 1 year back from start date.
2. Observe the first date and last date from DatesInPeriod function for highlighted row i.e., 1^st Jan 2007. First date is 2^nd Jan 2006 and last date is 1^st Jan 2007.
3. Observe the first date and last date from DatesBetween function for highlighted row i.e., 1^st Mar 2007. First date is 1^st Mar 2006 and last date is 1^st Mar 2007.
4. DatesInPeriod won’t consider both dates, it will exclude the start date and calculated exactly for 1 year.
5. DatesBetween will consider both dates inclusive.

The above 4 and 5^th points are important for how these 2 functions consider the period while calculation.

Thanks.