cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: how to calculate the age

Apologies, I didn't know it was a DAX/M discussion.

 

Why not use DATEDIFF with YEAR then: Age = DATEDIFF(Query1[DOB], Query1[Date],YEAR)

(DAX is not my specialism, but this looks to me like the better solution).

Specializing in Power Query Formula Language (M)
abhijeet Regular Visitor
Regular Visitor

Re: how to calculate the age

This will give a wrong answer when the month and day of the persons birth have not commenced yet in the current year. Example if date of birth of 6th Jul 2007 and if today is 6th Oct 2017 the person's age is 9 years but your DAX formula will return 10 years.

 

Using what I had suggested initially i.e. FLOOR(DATEDIFF([DoB], TODAY(), DAY) / 365.25, 1) will retun 9 years in the above example.

 

Also I know that my solution will have to wait for the entire day of birth to get over in any calendar year to give the right answer. What I mean is that if DoB was 6/10/2016 and today is 6/10/2017 then yes it will give 0. And the moment today's date turns to 7/10/2017 it will give the right answer i.e 1. Now in most of my models this is what I want, but of course if someone wants the age to be 1 on the date of birth they could always subtract 1 from DoB or add 1 to TODAY within the DAX. Their choice there....

Super User
Super User

Re: how to calculate the age

In that case you can also translate my solution to DAX:

 

Age = ROUNDDOWN(((10000*Year(Query1[Date])+100*Month(Query1[Date]) + Day(Query1[Date]))-(10000*year(Query1[DOB])+100*MONTH(Query1[DOB])+Day(Query1[DOB])))/10000,0)
Specializing in Power Query Formula Language (M)
avarma84 New Member
New Member

Re: how to calculate the age

This works great!