Forum Discussion
Turnover - Monthly Data Headcount
Hello!
I started using Power Bi 2 months ago, as I was recruited as an People Analyst for a company with many employees.
Im currently struggling with a project and im really grateful for any help you can give me!
-> We have an Headcount that is done monthly (Excel). Our Headcount consists in having the most important information about our current employees.
-> We have an dashboard that is updated automatically when a new Headcount file is pasted on a sharepoint folder. The goal is that the dashboard has information from diferent months/years.
-> In that dashboard is possible to select in a slicer every month that is present on the folder, so we can see data for different timelapses due to every file on the folder being uploaded to the file.
My task is to calculate the variance between months of employees numbers (Entrys and Exits) and identify which specifically which employees entered and, which ones left the company in a table. The most important would be to do this comparison with the previous month.
* Please take in consideration 2 important facts:
** Please take in consideration that our monthly headcount file is always 1 month later than the current month. We are currently in February so the latest Headcount file we have is from January.
** We dont have a column for the date that an employee left the company.
** We have a start date column.
I will post some pictures of examples of an headcount and what happens in the next file if there are entrys; what happens if some employees left the company.
Thank you really much for your help!
Hi Anonymous ,
I can be understood as finding out the data that is not duplicated in the two tables, you can check the following results:
Measure = CALCULATE(COUNT('new'[ID]),EXCEPT('new','old'))An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello! Thank you really much for your reply!
Basically each table represent a new headcount file.
Let's imagine the second table i posted is the January Headcount. The third one let's say its from February and it shows that two persons joined the company. Lets say that the first table is the March Headcount and it shows that 3 persons left the company.Every table i posted represents an Headcount file that is posted on the sharepoint folder and its automatically uploaded on the power bi makes me able to look at the "history" if I select a specific slicer on the report page. So this powerbi is a cumulative file that feeds on a lot of files.
The only way to see who left and who joined is when a new file is uploaded and we can compare if their employees id's are still there /or werent on the previous month. This would be easy on Excel with an XLOOKUP, but my manager needs it to be automatic on the powerbi.
The result that I would like would be that I could on the report table have a measure or some kind of table that shows specifically which employees left and which ones joined the company from one month to another. I need to calculate the difference from the total of employees in one month to another too.
Im not able right now to show some pictures of visually what would look like, but I will try as soon as possible!
Thank you so much for trying to help!
Hi Anonymous ,
I can be understood as finding out the data that is not duplicated in the two tables, you can check the following results:
Measure = CALCULATE(COUNT('new'[ID]),EXCEPT('new','old'))An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.