Edit

Share via

DATEDIFF

  • Article

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