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.
Being new to DAX, I researched various calculations of the age although I was specifically after calculating the year of retirement for a large dataset so that I could chart the staff demographics and profile.
I found the suggestion from Marco Russo (thank you Marco!) of the QUOTIENT method, which gives an accurate calculation of the age, and modified this to calculate the year of retirement as per the below. I hope you find this useful and if there are any enhancements, please do let me know.
Year of Retirement =
-- Please change the next variable from 65 to your organisation's retirement age
VAR RetirementAge = 65
VAR Birthdate = [Date of Birth]
VAR ThisDay = DATE(YEAR(TODAY()), 12, 31)
VAR IntBirthdate = YEAR ( Birthdate ) * 10000 + MONTH ( Birthdate ) * 100 + DAY ( Birthdate )
VAR IntThisDay = YEAR ( ThisDay ) * 10000 + MONTH ( ThisDay ) * 100 + DAY ( ThisDay )
VAR Age = QUOTIENT ( IntThisDay - IntBirthdate, 10000 )
VAR CheckedAge = DIVIDE ( Age, NOT ISBLANK ( Birthdate ) )
VAR YearstoRetirement = RetirementAge - CheckedAge
VAR ExpectedRetirementYear = YEAR(TODAY()) + YearstoRetirement
RETURN
ExpectedRetirementYear
Hi, @pjukone
Thank you for sharing. You could reply yourself and then accept your reply as solution. An answered thread is searched more easily than an open one. Others will learn more from your sharing.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |