DATEDIFF

How many interval boundaries (second, minute, hour, day, week, month, quarter or year) lie between two dates.

Group: Date and time. Compatible with DAX.

Syntax

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

Returns

Whole number. How many starts of a new interval lie between the two dates; negative when Date1 is the later one.

Remarks

• It counts calendar boundaries passed, not whole periods: from 31 December to 1 January is one year, from 1 January to 31 December of the same year none.
• A week begins on a Sunday, so from a Saturday to the next day is one week.
• For a day and anything longer, the time of day plays no part: from 23:00 to 01:00 the next morning is one day.
• When Date1 is after Date2, the count runs backwards and is negative. Analysis Services 2016 and earlier refuse such a pair with an error.
• A blank date gives BLANK.
• For the exact time between two date-times, subtract them: the difference is in days, and times 24 in hours.

Examples

Boundaries, not whole periods

EVALUATE
ROW (
    "31 Dec to 1 Jan, years", DATEDIFF ( DATE ( 2023, 12, 31 ), DATE ( 2024, 1, 1 ), YEAR ),
    "1 Jan to 31 Dec, years", DATEDIFF ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ), YEAR ),
    "31 Jan to 1 Feb, months", DATEDIFF ( DATE ( 2024, 1, 31 ), DATE ( 2024, 2, 1 ), MONTH ),
    "Saturday to Sunday, weeks", DATEDIFF ( DATE ( 2024, 3, 16 ), DATE ( 2024, 3, 17 ), WEEK )
)

Months from New Year 2026 to each bond's maturity

EVALUATE
SELECTCOLUMNS (
    Holdings,
    "Bond", Holdings[Bond],
    "Months left", DATEDIFF ( DATE ( 2026, 1, 1 ), Holdings[Maturity], MONTH )
)
ORDER BY [Months left]

Counting backwards

EVALUATE
ROW ( "Months", DATEDIFF ( DATE ( 2024, 3, 1 ), DATE ( 2024, 1, 1 ), MONTH ) )

Related functions

YEARFRAC, NETWORKDAYS, EDATE, DATE, INT.

Last updated 3 November 2026 · OpenBI Studio 0.3.74