Calculating age using 2 dates and then rounding down

[Mckenzie--Cl]

I'm trying to calculate the age of people at the time they were diagnosed with a health condition.  I have 2 dates, date of birth and date of diagnosis.  I've managed to do this using DATEDIFF but some of the ages being returned are wrong.  For example a person born on 1/12/1968 would have been 53 when they were diagnosed on 11/01/2022, however the DATEDIFF is returning an age of 54.  Is there a way that the number returned is rounded down to reflect the actual age at the time of being diagnosed?  Thanks.

[johnt75 · Accepted solution]

The reason DATEDIFF isn't working is because it counts the number of boundaries which have been crossed between the two dates, its not working to a particular number of days. A DATEDIFF between 31st Dec and 1st Jan gives 1 year, as 1 boundary has been crossed.

You could try instead using

Age =
YEAR ( 'Table'[Date of diagnosis] - 'Table'[Date of birth] ) - 1900

[Ahmedx · Accepted solution]

pls try this

[Mckenzie--Cl]

That seems to have worked!  Thanks very much Ahmedx.

[johnt75 · Accepted solution]

The reason DATEDIFF isn't working is because it counts the number of boundaries which have been crossed between the two dates, its not working to a particular number of days. A DATEDIFF between 31st Dec and 1st Jan gives 1 year, as 1 boundary has been crossed.

You could try instead using

Age =
YEAR ( 'Table'[Date of diagnosis] - 'Table'[Date of birth] ) - 1900

[Mckenzie--Cl]

That has worked.  Thanks very much Johnt75.