Calculation of year of retirement

[pjukone]

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

[v-zhangti]

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.