I’m trying to calculate FTE (full time equivalent) dynamically in Power BI based on the number of hours paid in a period.
In my scenario:
-
FTE is calculated as: [Total Hours Paid in Period] / [Total Standard Working Hours in Period].
-
[Standard Working Hours in Period] is 40 hours per week, and this is the standard amount covered by any payroll.
-
FTE can be reported for any period, and will include all payrolls where the payroll end date falls in the selected period, and will then be calculated across the total days within those payrolls e.g. if the selected period was 1-Mar to 31-Mar, it would include hours worked in the payroll period from 28 Feb 5 Mar (because end date of payroll is in March) but would not include hours worked in the payroll period 27 Mar to 3 Apr (because payroll end date is not in March)
-
There are two payrolls in the system – one for the warehouse and one for the office. Each is paid weekly but on a different day.
-
Casual employees will sometimes have no pay record within a pay period.
In the sample data below, you can see the two payrolls, and that Virat only worked in one period:
fact_Pay
dim_Payroll
The desired results for the full period
-
FTE for Warehouse payroll would be: 200 hours worked / 2 x warehouse pay periods * 40 hours = 200 / 80 = 2.5 FTE
-
FTE for Office payroll would be 80 hours worked / 2 x office pay periods * 40 hours = 80 / 80 = 1 FTE
-
Total FTE would therefore be 3.5.
The main issues I’m having are:
-
Working out the base hours for employees who didn’t work in a period, so that position level reporting is inaccurate
-
When working out overall FTE, only relating each record to the relevant payroll period (office or warehouse), and not double counting.
When I use a formula like this, I get incorrect results, as shown below:
FTE = SUM(fact_Pay[Hours Paid])/(40*DISTINCTCOUNT(dim_Payroll[Payroll end]))
I have attempted to create a minimum reproducible example here, but would greatly appreciate any solutions!