HR Analytics: Evaluate Rows For Employee Status

[hnguyen76]

Good afternoon,

I previously posted for employee status requesting to fill a bucket for employee status:
https://community.powerbi.com/t5/Desktop/HR-Employee-Status-CountRows/m-p/594999#M282523

However, the dynamics of the request has changed and it seems to be a bit more complicated now. I am receiving data for 2019 to determine the status of an employee. The new rules are as followed:

1. If your hire date is empty but it's your first entry in the data set and you're not on the new hire dataset then you're a "Transfer In"

2. If your hire date is before first date in dataset (1/1/2017) and it's your first entry in the dataset and you're not on the new hire dataset then you're a "Transfer In"

3. If your hire date is equal to YearMonth then your status is "New Hire"

4. if it's your last entry and you do not have any comments then you're a "Transfer Out"

5. If it's your last entry and you have a comment that contains "resign" or "termination" then you're "Termination"

6. You're a "Current Employee" if YearMonth is consecutive up until today's date

*NOTE: I do know that there might be other rules to this but the below sample should hopefully be a clear idea on what I am looking for.

Any help would be appreciated!

[v-cherch-msft · Accepted solution]

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

[hnguyen76]

Thanks, Cherie!

I took your concept and made it more dynamic but this was exactly what I was looking for! Thank you! You saved me countless hours of hair tearing stress!