DATEDIFF
Applies to: Calculated column Calculated table Measure Visual calculation
Returns the number of interval boundaries between two dates.
DATEDIFF(<Date1>, <Date2>, <Interval>)
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 |
The count of interval boundaries between two dates.
A positive result is returned if Date2 is larger than Date1. A negative result is returned if Date1 is larger than Date2.
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 |