Tracking Employees from Month to Month

[wnicholl]

Data Scenario –

We need to track employee movement from month to month. We are starting with a baseline file (Employee ID – Dec18) that has many employees; then we have monthly files (Employee ID – Jan19), etc. with employees for that month. Some employees might be terminated, and others can be new employees. When an employee is terminated in Jan19 we should not see them in Feb19. If a new employee is hired in Feb19 we should see them. 

Created -

We’ve built relationships between the files. The relationships we’ve created allows us to track terminated employees from month to month however, any new employees that are added to future months are not showing.

We created a table in Power BI that allows us to check for any new employees added to a future month.

(New Employees = EXCEPT('Feb18_EmployeeRoster','Jan18_EmployeeRoster')

We created a table visualization and pulled the “Employee ID” from baseline, then the monthly files Jan19, and Feb19.   

Issue –

When we add the “Employee ID” from the New Employees Table it does not allow us to see the newly added employees.  

If anyone can assist with a solution that allow us to track employee movement within the same visualization it would be greatly apricated!  Thank You!

[v-yulgu-msft · Accepted solution]

Hi @wnicholl ,

Please try this:

New Employees =
EXCEPT (
    VALUES ( 'Feb18_EmployeeRoster'[EmployeeID] ),
    VALUES ( 'Jan18_EmployeeRoster'[EmployeeID] )
)

Best regards,

Yuliana Gu

[wnicholl · Accepted solution]

Hi Yuliana - Thanks for repling to my post! I'll try your solution and let you know how it worked out.  

[wnicholl · Accepted solution]

Hi Yuliana - Thank you for your help. Your solutions worked great!!  Thanks agian... 

[v-yulgu-msft · Accepted solution]

Hi @wnicholl ,

Please try this:

New Employees =
EXCEPT (
    VALUES ( 'Feb18_EmployeeRoster'[EmployeeID] ),
    VALUES ( 'Jan18_EmployeeRoster'[EmployeeID] )
)

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana

I have a similar problem listed in the query, however each month I receive a new export of employees, which I have appended and added to the bottom of my table.

Is this possible to create a measure that will incorporate this. From this I understand I would possible need a new measure for each month?

Link to my query below with source files

https://community.powerbi.com/t5/Desktop/Track-movement-of-employees-Attrition-and-movers/td-p/89686...

[wnicholl · Accepted solution]

Hi Yuliana - Thanks for repling to my post! I'll try your solution and let you know how it worked out.  

[v-yulgu-msft]

Hi @wnicholl ,

Have you worked it out?

Regards,

Yuliana Gu

[wnicholl]

We had some issues with our data source and subsequently required us to remove our data files. We should be receiving our new data files sometime this week. Thanks for reaching out. I'll touch base once we've tested.