Comparing a month to it's relative previous month in a table

[Anonymous]

I have an "Employee" table similar to the following:

I need to take this table and check a month with it's relative previous month to see which employees existed in the previous month and which employees are new.  This helps me decide which employee should be marked as a hire and which should be marked as an employee that left the company.

For example, with the "Employee" table above, the results should look somewhat like this "Employee Status" table:

February compares it's data with January and notices that Employee ID 1 doesn't exist in February any more, so we need to mark it as "Left," while Employee ID 4 exists in February and doesn't in January, so Employee ID 4 would be a "Hire."  

This continues in March with Employee ID 3 that doesn't exist in March but did in February, so that employee is marked as "Left," and Employee ID 5 is new for March and did not exist in the previous month, so Employee ID 5 is a new "Hire."

Is it possible to create such an "Employee Status" table if all I have is the "Employee" table above?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may add calculated columns as follows.

Column =
VAR e = Employee[Employee ID]
VAR m = Employee[Month]
RETURN
    IF (
        m > MIN ( Employee[Month] )
            && NOT (
                CONTAINS (
                    Employee,
                    Employee[Employee ID], e,
                    Employee[Month], DATE ( YEAR ( m ), MONTH ( m ) - 1, 1 )
                )
            ),
        "Hire"
    )

Column 2 =
VAR e = Employee[Employee ID]
VAR m = Employee[Month]
RETURN
    IF (
        m < MAX ( Employee[Month] )
            && NOT (
                CONTAINS (
                    Employee,
                    Employee[Employee ID], e,
                    Employee[Month], DATE ( YEAR ( m ), MONTH ( m ) + 1, 1 )
                )
            ),
        "Left"
    )

[Anonymous]

Those calculated columns definitely gave me the basis for creating my analysis.  I appreciate the help!  Thank you!