DATEDIFF

Applies to: Calculated column Calculated table Measure Visual calculation

Returns the number of interval boundaries between two dates.

Syntax

DATEDIFF(<Date1>, <Date2>, <Interval>)

Parameters

Term	Definition
Date1	A scalar datetime value.
Date2	A scalar datetime value.
Interval	The interval to use when comparing dates. The value can be one of the following: - SECOND - MINUTE - HOUR - DAY - WEEK - MONTH - QUARTER - YEAR

Return value

The count of interval boundaries between two dates.

Remarks

A positive result is returned if Date2 is larger than Date1. A negative result is returned if Date1 is larger than Date2.

Example

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.

The following DAX query:

EVALUATE
VAR StartDate =  DATE ( 2019, 07, 01 )
VAR EndDate =    DATE ( 2021, 12, 31 )
RETURN
    {
        ( "Year",     DATEDIFF ( StartDate, EndDate, YEAR ) ),
        ( "Quarter",  DATEDIFF ( StartDate, EndDate, QUARTER ) ),
        ( "Month",    DATEDIFF ( StartDate, EndDate, MONTH ) ),
        ( "Week",     DATEDIFF ( StartDate, EndDate, WEEK ) ),
        ( "Day",      DATEDIFF ( StartDate, EndDate, DAY ) )
    } 

Returns the following:

Value1	Value2
Year	2
Quarter	9
Month	29
Week	130
Day	914