Ideas of Absenteeism Rate - Table Design

[AvPowerBI]

Hi,

The calculation for Absenteeism Rate based on the following url https://www.aihr.com/blog/absenteeism-rate/

is 

I had a look at HR Analytics example which has a section on Absenteesim

https://agile-hr-analytics.com/solution/?gclid=EAIaIQobChMIwvm9oZvd9gIVk63tCh20SAE-EAAYASAAEgLGf_D_B...

but not sure of the tables required?

Does anyone know what I would need to include?

Some of our Employees are on different shift patterns

i.e. Employee A could work Monday to Friday 9:00 to 17:30 (37.5 hours a week (with an hour lunch break))

     Employee B could be part time and only work Monday to Wednesday 9:00 to 17:30 (22.5 hours a week (with an hour lunch break))

Do I need a table something like this (purposely didn't do the full month of March but the table would have those dates)

Or can I do something clever in DAX to know the number of available workdays in a given period and should I just roll up the above table for the whole month telling me how many hours they were off sick for?

Any ideas would be much appreciated

Thanks

Thanks

[amitchandak · Accepted solution]

@AvPowerBI , Work days measure

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

You can use min max selected dates in place of start and end

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA