Forum Discussion

hnguyen76's avatar
hnguyen76
Resolver II
7 years ago
Solved

HR Employee Status CountRows

 

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!

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
      hnguyen76
      Resolver II
      7 years ago

      Good morning v-piga-msft

       

       

      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

       

      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

      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

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        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