How to identify new employees vs those that left

[Anonymous]

Each month I get a feed from HR (flat file) that has a list of all current employees for my department - everyone is identified with a unique code (PersonnelNumber).  When the data is refreshed in PowerBI each month I would like to automatically update two new columns in the dataset that would show me:

1) Column 'NewEmployee' flag (Yes/No, 1 or 0) - identify if the employee is new (did not appear in any previous month).

2) Column 'LastMonthofService' flag (Yes/No, 1 or 0) - identify if an employee is no longer showing up in the current month (would need to update the row for that employee in the previous month)

Would be great to also default the 1st period available in the dataset for "NewEmployee" to "N"

For example:

Thank you!!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

NewEmployee1 =
var _if=
IF(
    'Table'[Date]=MIN('Table'[Date]),"N",
CALCULATE(COUNT('Table'[Employee ID]),
FILTER(ALLSELECTED('Table'),
'Table'[Employee ID]=EARLIER('Table'[Employee ID])
&&'Table'[Date]>=EOMONTH('Table'[Date],-1)&&'Table'[Date]<=EARLIER('Table'[Date]))))
return
IF(
    _if=1,"Y","N")

LastMonthofService1 =
var _if=
CALCULATE(COUNT('Table'[Employee ID]),
FILTER(ALLSELECTED('Table'),
'Table'[Employee ID]=EARLIER('Table'[Employee ID])
&&'Table'[Date]<=EOMONTH('Table'[Date],+1)&&'Table'[Date]>=EARLIER('Table'[Date])))
return
IF(
    'Table'[Date]=MAX('Table'[Date]),BLANK(),
    IF(
    _if=1,"Y",
        "N")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you very much!  Works beautifully! 

[amitchandak]

@Anonymous , I have blog around Customer, In place of customer you use employee, You can use count(Table[Employee ID]) as your measure and try the formula

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

[Anonymous]

Thank you for that suggestion.  I tried to implement this and almost got it working but I would really prefer a way to implement this via adding columns to the table vs calculating it in a measure.  I want ability to create charts, graphs, totals, etc. and having a column there as a flag for each of the 3 scenarios just seems much easier.  I also noticed that in your example the "Retained" counts are not totalling up like the other two.