Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone
I'm doing a project for our HR team. I get sent 2 monthly reports that show current staff details (meaining I can calculate headcount) and absence data. I need to be able to show the % of staff that were absent in each month, based on the headcount in that particular month.
Does anyone have any ideas of how this can be done?
Many thanks
James
Hi @Anonymous ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi Frank
There is quite a bit of confidential data in the data set. But, to give you an idea of the datasets I have a table of Employee information and a table of absence information.
The employee table contains:
- Employee name
- Department they work in
- Start of employment
- End of employment (blank if they are still employed by the company)
- Salary
- Contracted hours
The absence table contains:
- Employee name
- Absence start date
- Absence end date
- Total working hours absent
- Absence type (sickness / holiday)
- Absence reason (if sick), e.g Flu
The expected result is:
- To see the % of serving employees that have been sick, YTD.
- To see the % of employees that have been sick in any given month this year, only for employees serving in that month.
I hope this helps and gives a bit of clarity & thanks for your time.
James
Hi James,
The report seems feasble.But then I would request you to give a dummy data sample...(I know this is troublesome, it would help the community over here to visualize your problem....lol)..Just put some random made-up numbers plus the fields that you have mentioned.
Regards,
nsa
Hi @Anonymous & @v-frfei-msft (and anyone else)
Apologies for the delay in getting you these. Please see below links to the files on onedrive.
Absence_Table:
Employee_Table:
Thanks again for your interest in this.
James
Hi James,
Please find the sample report attached..
https://www.dropbox.com/s/41zthxpbewvmoh9/Employee-Absentism%20DB%20Sample.pbix?dl=0
But there are two issues that need to be solved/looked at..
1. Including emp IDs whose leaves extends more than a month (i.e. if the start date is in January and end-date is in February)..we need to count the employee id for both the months.
2. The logic that I have applied considers all the active employees based on the termination dates..(If an employee left the firm and you would not be able to include that particular employee in the headcount for previous months..)
Will update if I can come up with solution for these..
Kind Regards,
nsa
I'd start off with a relationship between both tables on emp name (one to many).
No idea of how your data is strcutured physically since the links are gone but technically the emp table should only contain employees that have been sick. If not clean your data and have them removed.
If you DISTINCTCOUNT(AbsceneTable[employee]) that table you've got the amount of total employees that have been sick. The aforementioned measure can be divided by DISTINCTCOUNT(EmployeeTable[Employees]) to get the % of sick employees. This solves question one.
Question two is a bit more tricky. This can be done but I'd have to look at it tomorrow.
Good luck so far!
Hi @cnweke
Please see the links below.
I can get a number of employees (based on counting the number of rows or distinct values in the employee table. The problem I cant find a solution to is how to make that count dynamic, to show the total number of employees that were 'active' (working for the company) at any given time.
Thanks for your interest in this post.
James
Absence Table:
Employee Table:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |