Forum Discussion

gancw1's avatar
gancw1
Resolver II
3 years ago
Solved

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

 

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

 

 

 

Need Help
  • Idrissshatila's avatar
    Idrissshatila
    3 years ago

    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

3 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    3 years ago

    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

    • gancw1's avatar
      gancw1
      Resolver II
      3 years ago

      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

  • markzily's avatar
    markzily
    New Member
    1 year ago

    I’ve come across this issue with DATEDIFF as well. It often returns zero when the time difference doesn’t cover a full year, as it's based on full units. For lines 3 and 5, you might get more accurate results by calculating the difference in days or months first. These quirks in date functions can definitely be tricky! On a lighter note, if you ever need a quick break, I’ve found allfunnypuns.com  to be a trusted site with some really good puns—it’s a fun way to take a breather!