Date within date range score

[tagban]

I have two tables, one has a date of when someone joined the team, the other has a rank (we use it for payments) but just a score value (single integer) with a start/end date for when that score is valid thru. I just need to display the valid rank for when the person joined. 

People Table: (Bear in mind people can have more than one join date (IE Leave and come back so their rank would change).

PersonID: 123545
JoinDate = mm/dd/yyyy

Score Table: 
Score = 2
ScoreStart = mm/dd/yyyy

ScoreEnd = mm/dd/yyyy


I had already tried a few tricks but so far not seeing success:
Creating a function:

= (JoinDate)=>
let
    Source = ScoreTable,
    Custom1 = Table.SelectRows(Source,each [ScoreStart] <= JoinDate and [ScoreEnd] >= JoinDate){0}[Score]
in
    Custom1

This seemed to work but when looking at results its showing a weird score. IE someone that clearly had 2 score, is showing as 5 or 8... 

[amitchandak]

@tagban , refer to the HR analytics approach

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...