cancel
Showing results for
Did you mean:
Super User

## Calculating age in CDS entity

I could not find a simple solution to calculating age in a model driven app using a CDS entity because there is no RoundDown() function like there is in Canvas apps.  I was able to accomplish it by using two fields to create the third. crabe_dob is the Date of Birth field in the entity.  The first field (Age0) was a whole number calculated field with the formula

`` DiffInDays(crabe_dob, Now()) / 365``

This created a whole number that was automatically rounded up.

The second field (Age1) was a decimal field with the same formula.

The third field (Age) was a whole number field contained the following conditional formula

``````If Age0 is greater than Age1 then Set Age to  Age1 - 1
Else Set Age to Age0``````

Other solutions that I found involved using Flow, C++ or Javascript and I wanted to keep it simple for myself. Other commenters even said it wasn't possible OOB in Dynamics 365.

8 REPLIES 8
Community Support

Hi @Drrickryp ,

Best regards,

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

Thanks for posting the solution. There is one edge case where it may not work though, as it doesn't take into account leap years. A closer function would be the following, assuming that you're dealing with people who are currently alive (as the year 2000 is a leap year, but 1900 is not)

DiffInDays(crabe_dob, Now()) / 365.25

Super User

Thanks @DavidJennaway .  That fixes it for leap years.

Frequent Visitor

I wanted to add that if you use PowerFX Formulas (Currently in preview) you can set the column to be:

``RoundDown((DateDiff(Birthday,UTCNow(),TimeUnit.Days) / 365.25),0)``
Regular Visitor

Hey @Yoda11 🙂

What kind of output are you getting with that? A decimal number? I have a very similar formula and the age itself is being calculated correctly, the only issue is that it has decimal places. For example 33,00 or 43,00. Not ideal.

Regular Visitor
` DiffInDays(crabe_dob, Now()) / 365`

I think your example only works if crabe_dob is Date and Time type.

Ideally, a Date of birth column should be Date only.

Helper V

Why has Microsoft not resolved this on the contact record OOB - it seems like it is required by everyone I come across.

Regular Visitor

You can change the number of decimal places in the advanced options.

Announcements