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

HiteshDataXpert

1 year ago

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
    )

Forum Discussion

Measure based on value at selected time.

Create a Calculated Column to Determine the Correct Position

Update the Score Calculation

Related content

Set measure value based on slicer selection

Measure based on Multiple Slicer Selection

Calculate measure based on Filter selection

Select a measure based on a filter selection

DAX Measure based on selected filter value