Calculate a FTE based on maximum hours

[Dtstraten]

Hi,

I'm having a real problem calculating an average FTE. I want to break this down on a monthly basis where the maximum hours per week is 40 hours. So if you work 40 hours it's equal to one FTE. If you work 60 hours this is equal to 1,5 FTE.

I have the following fields that can be used:
- Date
- Name (employee)
- Hours
-Timecode

So far I have tried the following calculations:
* Date (bins) So this has been grouped by 7 days to calculate a week starting from 01/01/17
* Count weeks = DISTINTCOUNT ( 'Hours'[ Date (bins)])
* Calculated FTE = ([Hours]/40)/[Count weeks]
The issue with the calculated FTE above that this only work on an individual basis as it counts the number of weeks that the employee has been there. But when you look at from different projects basis this doesn't work. So if a project only have had 3 weeks where work has been performed then it will only count the 3 weeks. But as a fact it should always been looking at this for the full period instead.

So I tried to find a solution for this:
* Max weeks = CALCULATE([Count weeks], ALLEXCEPT( 'Hours', 'Hours'[Date].[Year]))
This helped me calculate it for the whole year correctly as it finds the max weeks to be 52 for 2017. But when you go down on a monthly basis it will use 52 weeks and not the maximum of weeks in that particular month. So again a dead end for me.

I hope that there is a simple way of doing this so PLEASE HELP ME. I am getting very desperate!

Thanks Danny

[v-shex-msft · Accepted solution]

HI @Dtstraten,

Maybe you can refer to below link if it works for your scenario:

Calculating Working hours

If above not help, please share some sample data with expected result to help us clarify your requirement.

Regards,

Xiaoxin Sheng