Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bdehning
Post Prodigy
Post Prodigy

DATEDDIFF Help

I am using the following to determine number of years between two dates in DAX.   I don't think my results are correct.  Is the measure, correct?   Both fields are Date Fields. 

  

YearDurationMeasure = DATEDIFF([Claimant Hire Date],[Loss Date],YEAR)
1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @bdehning 

It depends on what you are trying to do.

The function in the way you wrote it can work as a calculated column because it requires scalar values and returns the number of calendar years that were between the two values. In other words, if your two dates are within the same calendar year, for example 2023, you will get 0, and if one date is 2022 and the other 2023, you will get 1 even if it was only one month between the two dates.

If you are interested in accurately returning the number of years, including fractions, you can use the function:

Years_with_parts = ([Loss Date]-[Claimant Hire Date])/365
If you need a measure and not a calculated column you need to calculate the function on some aggregated form like :

datediff_measure = DATEDIFF(max('Table'[Claimant Hire Date]),max('Table'[Loss Date]),YEAR)
more information about Datediff is here:https://www.youtube.com/watch?v=_z1XSR7fRYA
Pbix with examples is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

4 REPLIES 4
Ritaf1983
Super User
Super User

Hi @bdehning 

It depends on what you are trying to do.

The function in the way you wrote it can work as a calculated column because it requires scalar values and returns the number of calendar years that were between the two values. In other words, if your two dates are within the same calendar year, for example 2023, you will get 0, and if one date is 2022 and the other 2023, you will get 1 even if it was only one month between the two dates.

If you are interested in accurately returning the number of years, including fractions, you can use the function:

Years_with_parts = ([Loss Date]-[Claimant Hire Date])/365
If you need a measure and not a calculated column you need to calculate the function on some aggregated form like :

datediff_measure = DATEDIFF(max('Table'[Claimant Hire Date]),max('Table'[Loss Date]),YEAR)
more information about Datediff is here:https://www.youtube.com/watch?v=_z1XSR7fRYA
Pbix with examples is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

What would be needed to add to your datedif file to show number of months from Claimant Hire Date and Loss Date?

Hi @bdehning 
Again, it depends on the needed logic.
If you need calendar months then :

Months = DATEDIFF([Claimant Hire Date],[Loss Date],MONTH)
If you need the differences including parts  :
months with parts = ([Loss Date]-[Claimant Hire Date])/30
If the second version should be a measure :
months_with parts_measure = SUMX('Table',DATEDIFF([Claimant Hire Date],'Table'[Loss Date],DAY)/30)
 
The updated pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

It worked fine and would months with parts be the most accurate measure to use to use number of months after claimant hire date.  I noticed Pandora shows 25 months for Sum of Months and 26.13 for Sum of months with parts.  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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