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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mayankkapoor85
Frequent Visitor

how to calculate the age

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. 2017-07-21 (1).png

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

2017-07-21 (2).png 

any help will be appreciated

2 ACCEPTED SOLUTIONS
Eric_Zhang
Microsoft Employee
Microsoft Employee

@mayankkapoor85

Try

 

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

View solution in original post

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

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

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
Advocate I
Advocate I

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
Microsoft Employee
Microsoft Employee

@mayankkapoor85

Try

 

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

@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.?

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

Thank you @Eric_Zhang : very helpful. 

 @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
Not applicable

This works great!

@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 

Specializing in Power Query Formula Language (M)

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

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.

Specializing in Power Query Formula Language (M)

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.

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)

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.

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

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) 

 

 

 

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)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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