Measure based on value at selected time.

Hi,

I am pretty new to Power BI and tasked with creating an employee dashboard to track current annual goal status. These goals are dependant on position and are converted to a score of 3, 2, or 1. Promotions happen once per year. I have been unable to figure out a solution to account for the position at the time of the date to affect the calculation.

Currently, the table just contains employee and position.

I was successfully able to use these nested if statements to check the employee's position and calculate the score based on the appropriate goals for the table above.

SCORE_1 = 
VAR CurrentValue = SELECTEDVALUE('Employees'[Position])
RETURN

IF(
    //Check Position 1
    CurrentValue = "Position 1",
    //IF True, calculate score
    (IF(
        [Average 1] >= 0.985,
        3,
        IF(
            [Average 1] >= 0.98,
            2,
            1)
        )
    ),
    //IF False, Check Position 2
    IF(
        CurrentValue = "Position 2",
        //IF True, calculate score
        (IF(
            [Average 1] >= 0.99,
            3,
            IF(
                [Average 1] >= 0.985,
                2,
                1)
            )
        ),
        //IF False, check Position 3
        IF(
            CurrentValue = "Position 3",
            //IF True, calculate score
            (IF(
                [Average 1] >= 0.995,
                3,
                IF(
                    [Average 1] >= 0.99,
                    2,
                    1)
                )
            ),
            //IF False, return 0
            0
        )
    )
)

To allow for historical data, I need to calculate their scores based on their position at the time by adding effective dates to the table.

I am struggling to capture the score based on the employee's position at the time. I've tried adding a SELECTEDVALUE date with no success. Going back prior to the latest date displays no data, and leaving it as current changes historical data when their position changes. 

The desired outcome would be as follows where the position change occurring 2/1/2025 updates the calculation only after that date:

(The same scores become lower based on higher goals associated with promotion on 2/1/2025, but remain the same score for prior to that date to reflect previous position).

Currently, updating the Employees table to reflect the new positions without a date would result in different scores as it uses the most recent position for all calculations:

I need calculations prior to 2/1/2025 to reflect the employee's position prior to the promotion, and data after 2/1/2025 to reflect the employee's current position so that historical data is maintained.

Any help would be greatly appreciated!

Thank you!