HR Analytics: Evaluate Rows For Employee Status

Hi hnguyen76

You may add an index column first. And then create Sort1,Sort2 columns as attached file.Then you will get the status column.

Status = 
VAR FirstDay =
    CALCULATE (
        FIRSTDATE ( Table2[YearMonth] ),
        ALLEXCEPT ( Table2, Table2[Emp ID], Table2[Sort2] )
    )
VAR LastDay =
    CALCULATE (
        LASTDATE ( Table2[YearMonth] ),
        ALLEXCEPT ( Table2, Table2[Emp ID], Table2[Sort2] )
    )
RETURN
    IF (
        Table2[Hire Date] = Table2[YearMonth],
        "New Hire",
        IF (
            OR (
                Table2[YearMonth] = FirstDay
                    && Table2[Hire Date] = BLANK (),
                Table2[YearMonth] = FirstDay
                    && Table2[Hire Date] <= DATE ( 2017, 1, 1 )
            ),
            "Transfer In",
            IF (
                Table2[YearMonth] = LastDay
                    && LastDay = DATE ( 2019, 1, 1 ),
                "Current Employee",
                IF (
                    Table2[YearMonth] = LastDay
                        && Table2[Comment] = BLANK (),
                    "Transfer Out",
                    IF (
                        Table2[Comment] IN { "End Contract", "Resignation" },
                        "Termination",
                        "Current Employee"
                    )
                )
            )
        )
    )

Regards,

Cherie