Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Second Last entry in data set

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...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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)