cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## DAX DATEDIFF funny behaviour

I am trying to calculate the duration between 2 dates and I observed some funny behavior with the DAX DATEDIFF() function

Diff / 365 = (End Date - Hire Date) / 365

DAX Year = DATEDIFF (Hire Date, End Date, YEAR)

DAX Month = DATEDIFF (Hire Date, End Date, MONTH)

Does anyone know why DATEDIFF returns zero year for line 3 and 5 ?

This is the data

 SN Hire Date End Date 1 2/5/1998 28/4/2023 2 12/12/2022 28/4/2023 3 21/2/2022 30/6/2022 4 12/12/2022 12/3/2023 5 7/3/2023 28/4/2023 6 12/12/2022 11/1/2023

1 ACCEPTED SOLUTION
Super User

Hello @gancw1 ,

yes, the datediff measure you're using is set do determine the difference between two dates by year, and in the line 3 and 5 the hire date and end date are in the same year which is 2022, thus the result is correct to be zero because it's in the same year.

 Vote for my Community Mobile App Idea

Proud to be a Super User!

2 REPLIES 2
Super User

Hello @gancw1 ,

yes, the datediff measure you're using is set do determine the difference between two dates by year, and in the line 3 and 5 the hire date and end date are in the same year which is 2022, thus the result is correct to be zero because it's in the same year.

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Helper III

Thanks for the explanation.
Seems like the DAX DATEDIFF( ) behaves differently from the Excel DATEDIF( ). In EXCEL row 2 to 6 will return 0 while DAX return 0 zero or 1 depending on the start/end year and not based on the actual duration