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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chris_C
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
TomMartens
Super User
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

View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

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:

Agefunct.jpgAge.jpgFinañ.JPG





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!
Paul on Linkedin.






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 🙂 !

 

 

@Romain_FOURNIER 

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!
Paul on Linkedin.






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

 

stavrospontikis
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

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

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

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.

@Chris_C,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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