how to calculate the age

[mayankkapoor85]

hello,

I am trying to create a column which calculates the age of the given person. i followed the below procedure 

1. create " new column"

u can see the formula in the image below. 

as you can see it gives me the above error , even when i move the today() function in the beginning. so instead i inserted today's date. it does calculate but it gives really weird calculations. pls see below

 

any help will be appreciated

[Eric_Zhang · Accepted solution]

@mayankkapoor85

Try

age = Year(today())- Year(Users[DateOfBirthday])

[Anonymous]

I create two columns:

One to indicate if on a check date (which might be Today), in the year of the check date, the person has already had birthday.

HasHadBirthday = OR(( Month(MyTable[DateOfBirth])==MONTH(MyTable[CheckDate]))*(DAY(MyTable[DateOfBirth])<=DAY(MyTable[CheckDate])),(MONTH(MyTable[DateOfBirth])<MONTH(MyTable[CheckDate])))

Computing the age is done with the check date year, minus the birth day year. If the person hasn't yet had their birthday in the check year, subtract 1.

AgeOnCheckDate = YEAR(MyTable[CheckDate]) - YEAR(MyTable[DateOfBirth]) - IF(MyTable[HasHadBirthday],0,1)

[kevnotec]

The correct, accurate formula is:

PersonalAge = IF(

     ISBLANK(Applications[PersonalDateOfBirth]), BLANK(),

     INT(YEARFRAC(Applications[PersonalDateOfBirth], TODAY())

     ))

You can verify the results using DAX Studio:

EVALUATE
SUMMARIZECOLUMNS (
    Applications[PersonalDateOfBirth],
    Applications[PersonalAge]    
)
ORDER BY MONTH(Applications[PersonalDateOfBirth]), DAY(Applications[PersonalDateOfBirth])

[Eric_Zhang · Accepted solution]

@mayankkapoor85

Try

age = Year(today())- Year(Users[DateOfBirthday])

[mayankkapoor85]

@Eric_Zhang : Thank you for the solution.It was really elegant. another question. some date of birth fields are blank and the value returned is 2017. Which conditional statement to use so that when 2017 is returned it inputs a null value in the field.?

[Eric_Zhang · Accepted solution]

age = IF(ISBLANK(Users[DateOfBirthday]),BLANK(),Year(today())- Year(Users[DateOfBirthday]))

[mayankkapoor85]

Thank you @Eric_Zhang : very helpful. 

[CAPEconsulting]

 @mayankkapoor85 @Eric_Zhang This would actually give the wroung answer where the day and month of the persons birth has not yet happened in the current year.

So what you should try is

INT(YEARFRAC(Ep[Date],TODAY()))

CAPE

[Anonymous]

This works great!

[MarcelBeug]

@CAPEconsulting dividing by 365.25 is not really a good idea either.

A correct Power Query solution can be found in this topic.

Basically the 2 dates are converted to a number with yyyyMMdd, subtracted and integer-divided by 10,000

Example: 20,171,006 - 19,620,421 = 550,585 => 55.0585 

[CAPEconsulting]

No don't think I agree with this approach. But will leave it to everyone's own judgement to trial whatever approach works best for them

[MarcelBeug]

So you don't agree with a correct solution? Strange.

Then you might as well disagree with the approach to add 2 numbers as number1 + number2.

This is just black and white mathematics, which is either correct or wrong. Nothing to (dis)agree.

There will be other correct solutions people may prefer, though.

Example: your solution Oct 6, 2016 to Oct 6, 2017 returns 0 instead of 1.

[CAPEconsulting]

Oh goodness. Its not the maths that I am talking about. I know the arithmentic here quite well. I mean the DAX vs M code. String and especially date manipulation in M code is far more tedious. Manipulation of and calculations in DAX are my prefernce. So that'ss what I am trying to say. No need to get nasty please.

[MarcelBeug]

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).

[kevnotec]

The DATEDIFF method is wrong, as DATEDIFF .. YEAR counts the number of year boundaries between the two dates.  i.e. the number of midnights on 1st January.

Please see my other post for correct formula.

[CAPEconsulting]

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. INT(YEARFRAC(Ep[Date],TODAY())) 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....

[vich2]

Your solution:  agetest = FLOOR(DATEDIFF(date(2017,02,05), date(2020,02,05), DAY) / 365.25, 1) is flawed.  It results in age 2, but should be 3 on their birthday.  There are no leap years yet it adjusts for one.

This is a direct solution that works:

age = IF(

   format([birthdate],"MMDD") <= FORMAT(TODAY(),"MMDD"),

  DATEDIFF([birthdate],TODAY(), YEAR),

  DATEDIFF([birthdate],TODAY(), YEAR) - 1) 

[MarcelBeug]

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)