Need help with DAX

Hi Anonymous 

You can create the following measures

1.Rank

Rank = RANKX(FILTER(ALLSELECTED('Table'),[Contract_EmployeeName] in VALUES('Table'[Contract_EmployeeName])),CALCULATE(MAX([completedOn])),,ASC,Dense)

2.Expected Create Date

Expected Create Date =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Contract_EmployeeName]
                IN VALUES ( 'Table'[Contract_EmployeeName] )
                    && [Rank] = 1
        ),
        [Category]
    )
VAR b =
    CALCULATE ( [Rank] )
VAR c =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Contract_EmployeeName]
                IN VALUES ( 'Table'[Contract_EmployeeName] )
                    && [Category] <> SELECTEDVALUE ( 'Table'[Category] )
                    && [Rank] < b
        ),
        [Rank]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Category] ) = a,
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Contract_EmployeeName]
                    IN VALUES ( 'Table'[Contract_EmployeeName] )
                        && [Rank] = 1
            ),
            [Created_On]
        ),
        CALCULATE (
            MAX ( 'Table'[completedOn] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Contract_EmployeeName]
                    IN VALUES ( 'Table'[Contract_EmployeeName] )
                        && [Rank] = c
            )
        )
    )

3.Expected End Date

Expected End Date = MAXX(FILTER(ALLSELECTED('Table'),[Contract_EmployeeName] in VALUES('Table'[Contract_EmployeeName])&&[Category] in VALUES('Table'[Category])),[completedOn])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.