DAX Date & Time - DATEDIFF function

Description

Returns the count of interval boundaries crossed between two dates.

DAX DATEDIFF function is new in Excel 2016.

Syntax

DATEDIFF (<start_date>, <end_date>, <interval>)

Parameters

Sr.No. Parameter & Description
1

start_date

A scalar datetime value.
2

end_date

A scalar datetime value.
3

interval

The interval to use when comparing the dates. The value can be one of the following −

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

Return Value

A whole number.

Remarks

If start_date is larger than end_date, an error value is returned.

The values given to the parameter interval are constants and not strings. Hence, they should not be enclosed in double quotation marks.

Example

= DATEDIFF (DATE (2016,1,1), DATE (2016,3,31), MONTH) returns 2. 
= DATEDIFF (DATE (2016,1,1), DATE (2016,4,1), MONTH) returns 3. 
= DATEDIFF (DATE (2016,1,1), DATE (2016,3,31), DAY) returns 90. 
= DATEDIFF (DATE (2016,1,1), DATE (2016,3,31), HOUR) returns 2160. 
= DATEDIFF (DATE (2016,1,1), DATE (2016,3,31), SECOND) returns 7776000.
dax_functions_date_time.htm
Advertisements