New User need help with formula

[4mkurtz71]

I am trying to forcast when an employee will retire.

Our employees usually retire with 30 years of service and are between 50 and 55. I have the data for years of service and date of birth. I am not sure how I would write the formula.

I want to show a range. 

25 years of service at age 50

26 years of service at age 51

etc.

Can anyone help me with the formula? I was thinking a if and then statement but I'm note sure.

[Daniel29195]

Hello @4mkurtz71 

can you please share a visual representation of what you would like to achieve ? 

best regards,

[4mkurtz71]

Now	age 55 & 30 years of service
in 1 year	age 55 & 30 years of service
in 2 years	age 55 & 30 years of service
in 3 years	age 55 & 30 years of service
in 4 years	age 55 & 30 years of service
in 5 years	age 55 & 30 years of service

[4mkurtz71]

The data I have is below. I am trying to forecast expected retirements. Most employees will retire at 30 years of service and age 55. I would like to forecast for the next 5 years.

How many employees are 50 and have 25 years of service

51 and 26 years of service

52 and 27 years of service

53 and 28 years of service

54 and 29 years of service

55 and 30 years of service

I want to slice it out in rank as well.

Thank you for any help you can provide.

EmplID

Name

Job Code

Job Title

Union Code

Birthdate

Age

Years of Service

Years till Age 55 & 30 Years Service

[v-cgao-msft · Accepted solution]

Hi @4mkurtz71 ，

Is this the expected output you want? Please check the pbix file.

If not, please consider posting more details and expected output. Thanks.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group