DatesInPeriod Vs DatesBetween – Power BI DAX

  • Post category:General
  • Post author:

Addend Analytics is a Microsoft Power BI Gold Partner based in Mumbai, India. Apart from being authorized for Power BI implementation Partner, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us

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 1st Jan 2006 to 31st 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

DatesA date column.
start_dateA date expression.
number_of_intervalsAn integer that specifies the number of intervals to add to, or subtract from, the dates.
IntervalThe 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
DatesA date column.
start_dateA date expression.
end_dateA 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., 1st Jan 2007. First date is 2nd Jan 2006 and last date is 1st Jan 2007.
  3. Observe the first date and last date from DatesBetween function for highlighted row i.e., 1st Mar 2007. First date is 1st Mar 2006 and last date is 1st 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 5th points are important for how these 2 functions consider the period while calculation.

Thanks.