Measure based on value at selected time.

[mgavin]

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.

Name	Position
Employee A	Position 1
Employee B	Position 1
Employee C	Position 1
Employee D	Position 1
Employee E	Position 2

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
        )
    )
)

Date	Employee	Average 1
1/1/2025	Employee A	0.988
1/1/2025	Employee B	0.99
1/1/2025	Employee C	0.97
1/1/2025	Employee D	0.983
1/1/2025	Employee E	0.987
2/1/2025	Employee A	0.988
2/1/2025	Employee B	0.99
2/1/2025	Employee C	0.97
2/1/2025	Employee D	0.983
2/1/2025	Employee E	0.987

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.

Name	Position	Effective Date
Employee A	Position 1	2/1/2024
Employee B	Position 1	2/1/2024
Employee C	Position 1	2/1/2024
Employee D	Position 1	2/1/2024
Employee E	Position 2	2/1/2024
Employee A	Position 2	2/1/2025
Employee B	Position 1	2/1/2025
Employee C	Position 1	2/1/2025
Employee D	Position 2	2/1/2025
Employee E	Position 3	2/1/2025

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:

Date	Employee	Average 1	Score
1/1/2025	Employee A	0.988	3
1/1/2025	Employee B	0.99	3
1/1/2025	Employee C	0.97	1
1/1/2025	Employee D	0.983	2
1/1/2025	Employee E	0.987	2
2/1/2025	Employee A	0.988	2
2/1/2025	Employee B	0.99	3
2/1/2025	Employee C	0.97	1
2/1/2025	Employee D	0.983	1
2/1/2025	Employee E	0.987	1

(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:

Date	Employee	Average 1	Score
1/1/2025	Employee A	0.988	2
1/1/2025	Employee B	0.99	3
1/1/2025	Employee C	0.97	1
1/1/2025	Employee D	0.983	1
1/1/2025	Employee E	0.987	1
2/1/2025	Employee A	0.988	2
2/1/2025	Employee B	0.99	3
2/1/2025	Employee C	0.97	1
2/1/2025	Employee D	0.983	1
2/1/2025	Employee E	0.987	1

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!

[HiteshDataXpert · Accepted solution]

Create a Calculated Column to Determine the Correct Position

In the scores table, create a calculated column to determine the employee's position at the time of the score. This column will use the Effective Date to find the correct position.

DAX

PositionAtDate = 
VAR CurrentDate = 'Scores'[Date]
VAR CurrentEmployee = 'Scores'[Employee]
RETURN
    CALCULATE(
        MAX('Employees'[Position]),
        FILTER(
            'Employees',
            'Employees'[Name] = CurrentEmployee &&
            'Employees'[Effective Date] <= CurrentDate
        )
    )

Update the Score Calculation

Modify your SCORE_1 measure to use the PositionAtDate column instead of the static Position column.

DAX

SCORE_1 = 
VAR CurrentPosition = SELECTEDVALUE('Scores'[PositionAtDate])
VAR CurrentAverage = SELECTEDVALUE('Scores'[Average 1])
RETURN
    SWITCH(
        TRUE(),
        CurrentPosition = "Position 1" && CurrentAverage >= 0.985, 3,
        CurrentPosition = "Position 1" && CurrentAverage >= 0.98, 2,
        CurrentPosition = "Position 1", 1,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.99, 3,
        CurrentPosition = "Position 2" && CurrentAverage >= 0.985, 2,
        CurrentPosition = "Position 2", 1,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.995, 3,
        CurrentPosition = "Position 3" && CurrentAverage >= 0.99, 2,
        CurrentPosition = "Position 3", 1,
        0
    )

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...