cancel
Showing results for
Did you mean:
New Member

## How to calculate the age in years from date of birth

I have a date of birth column called DOB and I am using the following calculation to get the age:

CustAge = DATEDIFF([DOB], TODAY(), YEAR)

However, in some cases it is giving me the wrong answer. For example for someone born on 30/12/1999 the age gets returned as 18 where obviously it should be 17 as todays date is 09/09/2017.

Does anyone know why I am getting the wrong answer or if there is a better alternative calculation?

Thanks

1 ACCEPTED SOLUTION
Super User

Hey,

from my point of view this thread provides the best solution:

https://community.powerbi.com/t5/Desktop/Age-and-Age-intervals-3-questions/td-p/27293

Regards

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
10 REPLIES 10
Super User

EDIT: ignore this. I see this same method was within the thread posted as a solution.

Can't you access Power Query? there is a function on the ribbon to calculate age. Duplicate the birthday column and then:

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

Thank you Chris for your useful answer.

Actually, many of my visuals already use the field I created by means of the 8 digits algorithm.

I would have liked :

→ your Age/Durations functions in PB Desktop too ;

→ The same behaviour for DAX DateDiff function thant the Excel DateDif function.

Thanks though for your responsiveness 🙂 !

Super User

Re: "→ your Age/Durations functions in PB Desktop too"

The Age/duration functions shown are from PBI Desktop (just launch Power Query)

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

To calculate the real age at a given date, the DateDiff DAX function returns :

→ not the real age at a given date (like DateDif Excel function)

but

→ the date at the end of year of the given date (contrary to DateDif Excel function)

Consequently, the DAX function is not appropriate.

Personally, I used the following algorithm :

https://www.sqlbi.com/blog/marco/2018/06/24/correct-calculate-of-age-in-dax-from-birthday/#:~:text=I...

The idea is to temporarily convert respectively year, month and day of the date on 8 digits.

Frequent Visitor

There is an even easier way, just go to the power query, click on the "Transoform" tab and at the "Number Column" section there is a function named "Rounding" select the one that is appropriate to you.

Thank you,

Stavros

Frequent Visitor

I use this calculation based on the T-SQL calculation (datediff is not availlable in my DAX version of the )

=(YEAR(TODAY()) - YEAR(Employee[Birthday])) - IF (  OR (   MONTH(Employee[Birthday]) > MONTH(TODAY()) ;   AND(MONTH(Employee[Birthday]) = MONTH(TODAY()) ;DAY(Employee[Birthday]) > DAY(TODAY()))   )   ;1  ;0 )

Super User

Hey,

from my point of view this thread provides the best solution:

https://community.powerbi.com/t5/Desktop/Age-and-Age-intervals-3-questions/td-p/27293

Regards

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
New Member

Thanks very much for your help. I solved the problem using:

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

The solution that suggested using rounding via the Transform tab does not seem to be possible in the current version of Power BI Desktop, i.e. the Transform tab no longer exists.

Close, but still not correct! There is an extra leap year every 100 years, as long as the year is evenly divisible by 400. Year 1900 was NOT a leap year. Year 2000 was a leap year. And there's no reason to use FLOOR if you format the column as a whole number.

Community Support

Glad to hear that you've solved this problem. You may help accept solution. That way, other community members may also benefit. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors