Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!)
Solved! Go to Solution.
i would use something like this to calulate the Date of Birth
Proud to be a Super User!
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
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?
i would use something like this to calulate the Date of Birth
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |