Hi,
I am trying to address an issue we have regarding the tracking of users across teams and tracking changes to peoples roles within teams.
Currently we track to the movement of employees across teams and changes in roles using 3 excel files. I need to use this information to understand which role they had, what location they worked from and what team they were apart of as well as who the team lead at the time of the case completion was.
The 4 files I am using are:
1) Employee - this is a sheet that contains all employee names, the date they joined the firm and their employee IDs
Note Employee ID is the unique field
2) Teams - this sheet has the name of the team, the team leader, the date the Team Leader took the role as team lead, team ID and Employee ID of said team leader. Everytime a new Team Lead is appointed this list is updated with a date and the new team leads name and ID input on a new row.
3) Employee Attributes - This contains the Employee Name, ID, Role, Location and Cell ID they are apart of and the date of the most recent change. This is updated every time a change is made to an employees team, role or location by adding a new row and dating it.
4) Case Information - This contains case information for which analyst completed which case and when
Again, I need to identify which team the Employee was apart of when the case was completed, what there role was and which location they worked from at that time.
I cannot work out how to do this and would appreciate any guidance.
Also I would appreciate any feedback on structuring the data sets if you feel there is a better way of doing this to achieve the goal.
I cannot share the actual datasets due to client privacy but I have put together a dummy dataset. I am aiming to put this into Power BI to show how employees performed in different roles and across different teams/team leads.
Thank you
