cancel
Showing results for
Search instead for
Did you mean:
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. 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

2 ACCEPTED SOLUTIONS

Accepted Solutions Eric_Zhang
Moderator

## Re: how to calculate the age

@mayankkapoor85

Try

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

## Re: how to calculate the age

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

## Re: how to calculate the age

@mayankkapoor85

Try

`age = Year(today())- Year(Users[DateOfBirthday])`
mayankkapoor85 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.?

Highlighted Eric_Zhang
Moderator

## Re: how to calculate the age

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

## Re: how to calculate the age

Thank you @Eric_Zhang : very helpful.

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

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

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

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