DATEDIFF Function (DAX)

How does the DATEDIFF function (DAX) work?

The DATEDIFF function (DAX) returns the count of interval boundaries crossed between two dates.

DATEDIFF Formula Syntax

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

How do you use the DATEDIFF function?

Used this function to determine the number of days between today and the first day of the year. You could also calculate the number of months between date values.

Related Blog Posts

Related Support Forum Posts

DateDiff to only calculate number of work days
Datediff workday only
DATESBETWEEN, DATESINPERIOD, DATEDIFF functions

Considerations when using the DATEDIFF function

The DATEDIFF function accepts three arguments: date_part, start_date, and end_date.

date_part is the part of date e.g., a year, a quarter, a month, a week that you want to compare between the start_date and end_date.

start_date and end_date are the dates to be compared. They must be resolved to values of type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME.

Related Video Tutorials

Formula examples using the DATEDIFF function

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], second ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], minute ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], hour ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], day ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], week ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], month ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], quarter ) ) DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], year ) )

= 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.

DATEDIFF([StartDateTime],[EndDateTime], DAY) – ( CALCULATE ( COUNTROWS(‘Weekends’), ‘Weekends'[IsWeekend] = TRUE( ), DATESBETWEEN(‘Weekends'[Date], [StartDateTime],[EndDateTime]) ) )

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership