HR Employee Status CountRows

[hnguyen76]

Good morning!

In 2019, I will have a list of employees to determine if they are current employees, new hires, transfer in, transfer out, or terminated. I was able to count all rows for each employee ID but I'm not sure how to drop them into specific buckets. The rules are:

Current Employee
If end of previous year you were an employee and count of rows of current year is equal to month number then you're current employee

New Hire / Trans In
If previous year you were not an employee and in January of current year you were an employee then you're a new hire

Termination / Trans Out
If previous year you were an employee and the count of rows of current year is less than month number then you're either a trans out or terminated

I currently have this in a calculated column:

EmployeeStatus = 
var CurrentYear = MAX(Sheet1[YearNum])
var CurrentMonth = CALCULATE(MAX(Sheet1[MonthNum]), CurrentYear = Sheet1[YearNum])
var CountAllRows = CALCULATE(COUNTROWS(Sheet1), FILTER(Sheet1, Sheet1[ID]=EARLIER(Sheet1[ID]))) 
return 
CountAllRows

Any help would be gladly appreciated!

[v-piga-msft]

Hi @hnguyen76,

Based on your logic, you could create the measure with the formula below. 

Measure = 
IF (
    MAX ( 'Table1'[EmployeeStatus] ) > 1
        && MAX ( 'Table1'[EmployeeStatus] ) = MAX ( 'Table1'[MonthNum] ),
    "Current Employee",
    IF (
        MAX ( 'Table1'[EmployeeStatus] ) = 1
            && MAX ( 'Table1'[YearNum] ) = YEAR ( MAX ( 'Table1'[YearMonth] ) ),
        "New Hire / Trans In",
        IF (
            MAX ( 'Table1'[EmployeeStatus] ) > 1
                && MAX ( 'Table1'[EmployeeStatus] ) < MAX ( 'Table1'[MonthNum] ),
            "Termination / Trans Out",
            "others"
        )
    )

However, I'm afraid that your logic have a little confuse between Current Employee and Termination / Trans Out.

You could modify the measure as your requirment.

If you still need help, please share your data sample and your desired output so that I can understand your logic better and could help further on it.

Best  Regards,

Cherry

[hnguyen76]

Good morning @v-piga-msft,

I most definitely would love some additional help. Sorry that my original post was not clear enough. Since I'm behind a corporate network I cannot attach a file to send as a sample but I'll copy and paste the table here so it would be easier to work with:

YearMonth	ID	YearNum	MonthNum	EmployeeStatus
1/1/2018	100	2018	1	3
2/1/2018	100	2018	2	3
3/1/2018	100	2018	3	3
1/1/2018	106	2018	1	13
2/1/2018	106	2018	2	13
3/1/2018	106	2018	3	13
4/1/2018	106	2018	4	13
5/1/2018	106	2018	5	13
6/1/2018	106	2018	6	13
7/1/2018	106	2018	7	13
8/1/2018	106	2018	8	13
9/1/2018	106	2018	9	13
10/1/2018	106	2018	10	13
11/1/2018	106	2018	11	13
12/1/2018	106	2018	12	13
1/1/2019	106	2019	1	13
10/1/2018	122	2018	10	4
11/1/2018	122	2018	11	4
12/1/2018	122	2018	12	4
1/1/2019	122	2019	1	4
6/1/2018	127	2018	6	1
6/1/2018	128	2018	6	1
12/1/2018	131	2018	12	2
1/1/2019	131	2019	1	2
1/1/2019	134	2019	1	1
1/1/2019	135	2019	1	1
5/1/2018	136	2018	5	4
6/1/2018	136	2018	6	4
7/1/2018	136	2018	7	4
8/1/2018	136	2018	8	4
8/1/2018	137	2018	8	6
9/1/2018	137	2018	9	6
10/1/2018	137	2018	10	6
11/1/2018	137	2018	11	6
12/1/2018	137	2018	12	6
1/1/2019	137	2019	1	6

If we were to look at this data sample the optimal outcome should look like the following considering that we're in 2019:

ID 100: Termination / Trans Out
ID 106: Current Employee
ID 122: Current Employee
ID 127: Termination / Trans Out
ID 128: Termination / Trans Out
ID 131: Current Employee
ID 134: New Hire
ID 135: New Hire
ID 136: Termination / Trans Out
ID 137: Current Employee

Based on your current measure the outcome I receive is:

ID 100: Current Employee
ID 106: others
ID 122: Termination / Trans out
ID 127: New Hire / Trans In
ID 128: New Hire / Trans In
ID 131: Termination / Trans Out
ID 134: New Hire / Trans In
ID 135: New Hire / Trans In
ID 136: Termination / Trans Out
ID 137: Termination / Trans Out

Thanks in advanced for the help!

[v-piga-msft · Accepted solution]

Hi @hnguyen76,

Sorry for the delay.

Please try this measure below.

measure_ = 
VAR maxyear =
    CALCULATE ( MAX ( 'Sheet1'[YearNum] ), ALLEXCEPT ( Sheet1, Sheet1[ID] ) )
VAR currentyear =
    YEAR ( TODAY () )
RETURN
    IF (
        maxyear < currentyear,
        "Termination / Trans Out",
        IF (
            maxyear = currentyear
                && MAX ( 'Sheet1'[EmployeeStatus] ) = 1,
            "New Hire",
            IF (
                maxyear = currentyear
                    && MAX ( 'Sheet1'[EmployeeStatus] ) > 1,
                "Current Employee"
            )
        )
    )

Here is the putput.

You also could refer to my test pbix.

Best  Regards,

Cherry