I am trying to create a calculated column in power BI called most recent score that gives me the most recent score for each employee.
Employee Number Date Score Most recent score
1234 01/01/2019 1 1
1235 01/01/2019 4 2
1236 01/01/2019 2 3
1288 01/01/2019 0 0
1259 01/01/2019 0 1
1234 01/02/2019 3 1
1235 01/02/2019 4 2
1236 01/02/2019 1 3
1288 01/02/2019 2 0
1259 01/02/2019 4 1
1234 01/03/2019 1 1
1235 01/03/2019 2 2
1236 01/03/2019 3 3
1288 01/03/2019 0 0
1259 01/03/2019 1 1
1234 01/04/2019 BLANK 1
1235 01/04/2019 BLANK 2
1236 01/04/2019 BLANK 3
1288 01/04/2019 BLANK 0
1259 01/04/2019 BLANK 1
I am using the below measure which seems to work unless the most recent score is a "0" in which case it pulls through the most recent non "0" score.
Most Recent Score =
VAR MRSM = Master[Employee ID]
RETURN
CALCULATE (
LASTNONBLANK ( Master[Score], Master[Score] ),
FILTER ( Master, Master[Employee ID] = MRSM )
)
Any help would be appreciated
EDITED ANSWER
This seems to do what you need.
Most Recent Score =
VAR EmpID = 'Master'[Employee ID]
VAR tblScores =
FILTER ('Master', 'Master'[Employee ID] = EmpID && NOT ( ISBLANK ( 'Master'[Score] ) )
)
VAR mrsDate = CALCULATE ( MAX ( [Date] ), tblScores )
RETURN
CALCULATE ( MAX ( 'Master'[Score] ), FILTER ( tblScores, 'Master'[Date] = mrsDate )
)