Utilisation by staff - accounting for leavers + joiners + expected hours required to work

[4c_em]

Hi Guys

I am working on greating a staff utilisation report which looks at;

1. actual hours logged vs expect hours as a percentage

2. expected hours to be pro rata if staff leaver or join in that period from;

a) hire date to last day of the month

b) start date to termination date

I am coming stuck on point 2, I can share my data if someone wants to have a look. The formula I am using is below

Total Expected = if('Timecard Split'[Year-Mon - LDoTM]='Timecard Split'[Year-Mon - Hire Date],CALCULATE(sum('Calendar'[Weekday?]),DATESBETWEEN('Calendar'[Date].[Date],'Timecard Split'[Hire Date],'Timecard Split'[Year-Mon - LDoTM]))*8,if('Timecard Split'[Year-Mon - LDoTM]='Timecard Split'[Year-Mon - Termin. Date],CALCULATE(sum('Calendar'[Weekday?]),DATESBETWEEN('Calendar'[Date].[Date],'Timecard Split'[Start of Month],'Timecard Split'[Termination Date]))*8,IF(AND('Timecard Split'[Year-Mon - LDoTM]<>'Timecard Split'[Year-Mon - Hire Date],'Timecard Split'[Year-Mon - LDoTM]<>'Timecard Split'[Year-Mon - Termin. Date]),'Timecard Split'[ExpectedMonthHOurs])))

LDoTM = last day of the month

Also let me know if you need anything else to add.

Many thanks

Ebrima

[v-shex-msft · Accepted solution]

HI @4c_em,

It seems like you calculate available working hours between two date column, I'd like to suggest you to take a look at below link which has similar requirement:

Calculating Working hours

Regards,

Xiaoxin Sheng