Return latest value present in power BI

powerbi dax nonblank
Jody picture Jody · Sep 24, 2018 · Viewed 9.4k times · Source

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.

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

Answer

Marco Vos picture Marco Vos · Sep 24, 2018

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

Related questions

how to convert a Integer to Text value in Power BI

I am creating a calculated column in existing power BI report. the calculated column concatenates integer & text columns. I tried below query which give syntax error to me, = Number.ToText(table1.[RegionID]) & " " & table1.[RegionName] I tried some …

Read More
How to format new measure (not column) in power BI as percentage?

When I create a new measure with formula, i.e. sum(col1)/max(col2), it automatically converts the result into #.#% format. Strangely, it doesn't happen all the time; sometimes it just gives result #.## format. Any idea of how to format …

Read More
How to get month name from month number in Power BI?

I have Year number and Month Number in my data. How using DAX can I get the month name out of month number? In SSRS its very easy. But how to achieve that using DAX?

Read More