Calculate the Number of people that will be retired

[Morganius]

Hi, 

I have the following problem and I can not find my way out... 

I have a table with the Employees information, some basic info like below:

Now, based on the following 2 slicers, I would like to know whether this employee will be retired or not.

Slicer 1: Number of Year (0 to 10) --> This is the number of years in the future.

Slicer 2: Retirement Age (60 to 70) --> Age when you will get retired.

My final goal is to be able to show per Company how many people will be retired considering the selection done in the 2 previous slicers. Any thoughs?

I was trying with a measure like below but I got stucked while considering all the Ages and companies...

Is_Retired? =

VAR NUM_YEAR = SELECTEDVALUE(years[Number of Years])

VAR Ret_Age = SELECTEDVALUE('Ret Age'[Retirement Age])

VAR Math = IF((Min('Employe'[Age ]) + NUM_YEAR - Ret_Age)>=0,"YES","NO")

return Math

Thanks.

[Anonymous · Accepted solution]

Hi @Morganius ,

You could try this meausre:

Number of retirees per company = 
var _reage=SELECTEDVALUE('Slicer 2'[Retirement Age])
var _year=SELECTEDVALUE('Slicer 1'[Number of Year])
return CALCULATE(COUNT('Table'[Name]),FILTER(ALLSELECTED('Table'),[Company]=MAX('Table'[Company])&&[Age]+_year>=_reage))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.