HR Dashboard Data Modelling of Active Employees

[Anonymous]

Hi all,

I am in the process of developing a HR dashboard for my organisation and am struggling to get my head around the way I should set up the data; I would just like to simply move the new sheets over, click refresh, and the terminations for example can be updated.

Once set up, I am hoping to calculate the following: Employee turnover, Attrition rate, number of new hires in a month, number of exits in a month. Further to this, I would like to have historical data as well so in 6 months - a years time, we can see the turnover over time.

I have two excel spreadsheets that I receive on a monthly basis which contains the following:

Spreadsheet 1 - all active employees as of a certain date (does not just contain new starters, all employees who are active in the system)

Sample data:

Employee ID	Employee Name	Hire Date	Location
1	Joe Bloggs	20 March 2020	NY
2	Kevin Smith	30 April 2018	SF

Spreadsheet 2 - Monthly Terminations

Employee ID	Employee Name	Leave Date	Term Reason
4	Steph Beatrice	3 Jan 2021	Relocation
5	Brad Ford	1 Jan 2021	Better work conditions

At the end of every month, I would just like to place the updated listing in the correct folder, click refresh, and then calculations are possible.

My question is: how would you recommend the data is set up given the two spreadsheets that I have available to me on a monthly basis. I was thinking that maybe I needed to append the 'active employee' data, but am not sure.

Your help is much appreciated. Let me know if you need me to reexplain something, but hopefully I have explained this enough.

[mahoneypat]

I would approach this as follows:

- make a query that appends all your active employee files (with combine & edit feature) and remove duplicates

- make another query that appends all your termination files (and remove duplicates if applicable)

- merge the second query into the first on the employeeID column, and expand the termination date and reason columns

- disable load on the termination tables

- load the table with employees data and terminations to your model for analysis/visualization

Regards,

Pat

[Anonymous]

Hi Pat,

Thanks for that, are you suggesting in the end I would only have one query that would contain both termination dates from merging this together?

[amitchandak]

@Anonymous , You do not have a termination in the first table ? that is the part second table?

Can you share a better sample with the same employees? 

Refer my HR blog, if this can help a bit -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@amitchandak Unfortunately, the data that has been given to me does not contain an end date (Spreadsheet 1), and I can only get the end date from Spreadsheet 2 which has the monthly terminations

[amitchandak]

@Anonymous , Create a new column in table 1

Leave Date = Maxx(filter(Table2, Table1[Employee ID] = Table2[Employee ID]), table2[Leave Date])

Now you can use the blog I mentioned

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://www.youtube.com/watch?v=e6Y-l_JtCq4