Skip to main content
cancel
Showing results for 
Search instead 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

Reply
gancw1
Helper III
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)

 

gancw1_3-1682667188464.png

 

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

 

This is the data

 

SNHire DateEnd Date
12/5/199828/4/2023
212/12/202228/4/2023
321/2/202230/6/2022
412/12/202212/3/2023
57/3/202328/4/2023
612/12/202211/1/2023

 

 

 

1 ACCEPTED SOLUTION
Idrissshatila
Super User
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.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Idrissshatila
Super User
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.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.