Return latest value present in power BI

10.5k views Asked by At

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

1

There are 1 answers

3
Marco Vos On BEST ANSWER

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

enter image description here