I want to compare data of 2 consecutive months to see if the there are any employee movements and want to capture those movements as output. The movements are as mentioned below
- Exit - If someone has left the company (appears in previous month , but not in current month data)
- Entry - If someone new has joined the company (appears in current month , but not in previous month data)
- Transfer in - If someone has moved from one position to another position within the organization (the output should show new position related data)
- Transfer Out - If someone has moved from one position to another position within the organization (the output should show old position related data)
Here are the sample files (there are multiple other columns in the original file)
Let me explain each of the employee movement as you can see in the ouput file
- Employees appearing in both files need not to be captured (we want to capture only employee movements)
- Sunil Shetty has 2 positions (with Full time equivalent value 0.5 and 0.5) in previous month, however in current month his position and org ID has changed for one of the positions. In this case He moves out from position 8888 and gets transferred to position 9998. Hence his movement has been captured as 'transfer out' from the position 8888 and at the same time his movement has been captured as 'transfer in' in the position 9998
- Virat Kohli is also working on 2 positions (with full time equivalent value 0.6 and 0.4) , one of the position (5555 gets delimited. The movement has been captured as 'transfer out' (not as exit because , he still appears in both month data due to other position)
- Rohit Sharma appears as Exit, because he is not in current month data
- Hardik Pandya appears as 'Transfer in' , though he is on the same position, his FTE value has increased by 0.2 FTE
-Ravindra Jadeja appears as Entry, because he is not in previous month data.
How to create the output using powerBI? Is it possible ? How to do it?