Second Last entry in data set

[Anonymous]

My data contains submit date, valid entry and RAG status, I want rag status of employees with valid entry yes and date submit is second last.

DAX should filter the valid  = Yes, and Employee Code = Employee Code and provide rag status of second max date in submit date.

Below is table, first four column I have in table and I want to add Previous RAG column.

Date Submit	Emp Code	Valid	RAG	Previous RAG
1-Jan-2019	123	Yes	Red	Green
18-Dec-2018	123	Yes	Green
1-Jan-2019	123	No	Red
1-Dec-2020	1444	Yes	Red	Amber
1-Nov-2018	1444	Yes	Amber

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can create two Calculated columns.

Rank By Date =

VAR _rank =

    RANKX (

        FILTER (

            'Table',

            'Table'[Valid] = "Yes"

                && 'Table'[Emp Code] = EARLIER ( 'Table'[Emp Code] )

        ),

        'Table'[Date Submit],

        ,

        DESC,

        DENSE

    )

RETURN

    IF ( 'Table'[Valid] = "No", BLANK (), _rank )

Previous RAD =

VAR val =

    MAXX (

        FILTER ( 'Table', [Rank By Date] = 2 && [Emp Code] = EARLIER ( [Emp Code] ) ),

        [RAG]

    )

RETURN

    IF ( 'Table'[Rank By Date] = 1, val, BLANK () )

The result looks like this:

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a new column like

new column =
var _rank = rankx(filter(Table, [Emp] = earlier([Emp ])), [Date Submit],,desc, dense)
Var _status = maxx(filter(Table, [Emp] = earlier([Emp ]) && _rank =2),[status])
return
if(_rank =1,_status, blank())

if _rank does not work create a rank column first

rank = rankx(filter(Table, [Emp] = earlier([Emp ])), [Date Submit],,desc, dense)