Need a calculated column to return a person's age based on a certain date column

[grggmrtn]

Our person ID's are represented by a 10 digit number - the first 6 is the person's day of birth (ddmmyy) and the last 4 are unique. So, since I was born on the 12th of may 1971, my ID would be 120571XXXX.

Each row of our tables has a DaysList, which is a unique date calculated from a start and a stop date, incrementing by one.

I need a measure that will tell me the age of the person on each [DaysList]

Any ideas? (and thanks in advance!)

[judspud]

Hi @grggmrtn 

You could create a calculated column which takes the LEFT 6 digits from their ID and formats this as a date.

Then a simple Datediff function between their D.O.B and the dayslist to get the number of years i.e. their age

Hope this makes sense 🙂

Thanks,

George

[grggmrtn]

Hey @judspud 

Makes perfect sense, except that I'm going to have problems with people born after 1999 😉

Side question: What if I wanted to link that age to my Date table instead?

[AnthonyTilley · Accepted solution]

i would use something like this to calulate the Date of Birth 

DOB =

--Get the 6 digit date from ID

var full = Left(Employee[ID],6)

-- get the day from the 6 digit date

Var d = value(Left(full,2))

-- get the month from the 6 digit date

var m = value(right(left(full,4),2))

-- get the year from the six digit date

var y = VALUE(right(full,2))

-- get the current date and subtract 2000 to get the 2 digit year for this year

var this_Year = year(now())-2000

-- check to see if the year in the 6 digit date is less than this years two digits if it is then they are a 2000 if not then they are a 1900 as no one could be born after this year

var y_ADD = if(y<=this_Year,2000,1900)

-- add the eaither 1900 or 2000 to get a 4 digit year

var y2 = y+y_ADD

-- create a date from the three parts calulated above

Var DOB = date(y2,m,d)

-- return the date of birth

return DOB

Then just use a diffrence between this and your other date colunm

[judspud]

Good point @grggmrtn 

I would suggest implementing a check to look at the final 2 digits of the 6 digit string and then expanding to prefix with either 19 or 20.

The only issue with this will be in the future when you get to the year 2095 etc

How do you mean linking to your date table?

Thanks,

George