Human Resources - Absence Reporting

[Anonymous]

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

[v-frfei-msft]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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:

https://cclvehicle-my.sharepoint.com/:x:/g/personal/james_bockett_hertzaccidentsupport_com/ETKvdRJS5b9Jll8mySd4gUEBtusT8qILaVxB9MWZiNr1QQ?e=kTvNvz

Employee_Table:

https://cclvehicle-my.sharepoint.com/:x:/g/personal/james_bockett_hertzaccidentsupport_com/EUZ0pPth6JRPmI4XBkfO5x8BSy6PRn3isyQsRBWywwRfFg?e=aR4C4W

Thanks again for your interest in this.

James

[Anonymous]

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

[Anonymous]

Thanks very much for your help @nsa

@v-frfei-msft did you have any luck?

Many thanks

James

[cnweke]

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!

[Anonymous]

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:

https://cclvehicle-my.sharepoint.com/:x:/g/personal/james_bockett_hertzaccidentsupport_com/ETKvdRJS5...

Employee Table:

https://cclvehicle-my.sharepoint.com/:x:/g/personal/james_bockett_hertzaccidentsupport_com/EUZ0pPth6...