cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayankkapoor85 Frequent Visitor
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

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: how to calculate the age

@mayankkapoor85

Try

 

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

Re: how to calculate the age

age = IF(ISBLANK(Users[DateOfBirthday]),BLANK(),Year(today())- Year(Users[DateOfBirthday]))
13 REPLIES 13
Moderator Eric_Zhang
Moderator

Re: how to calculate the age

@mayankkapoor85

Try

 

age = Year(today())- Year(Users[DateOfBirthday])
mayankkapoor85 Frequent Visitor
Frequent Visitor

Re: how to calculate the age

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

Moderator Eric_Zhang
Moderator

Re: how to calculate the age

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

Re: how to calculate the age

Thank you @Eric_Zhang : very helpful. 

abhijeet Regular Visitor
Regular Visitor

Re: how to calculate the age

 @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

either FLOOR(DATEDIFF([DOB], TODAY(), DAY) / 365.25, 1)

or ROUNDDOWN(DATEDIFF([DOB], TODAY(), DAY) / 365.25, 0)

 

Abhijeet

Highlighted
Super User
Super User

Re: how to calculate the age

@abhijeet 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)
abhijeet Regular Visitor
Regular Visitor

Re: how to calculate the age

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
Super User
Super User

Re: how to calculate the age

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)
abhijeet Regular Visitor
Regular Visitor

Re: how to calculate the age

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.