Forum Discussion
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.
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=_z1XSR7fRYAPbix with examples is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quicklyHi 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])/30If 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 attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
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=_z1XSR7fRYAPbix with examples is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quicklyWhat 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])/30If 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 attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
