powerbidaxnonblank

Return latest value present in power BI


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


Solution

  • 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