Steps to implement FTE Logic in Power BI

by

✅ What You’ll Need

  1. Employee Table with fields like:
    • Employee ID, Start Date, End Date, FTE, Status, Leave Type, etc.
  2. Calendar Table with:
    • Month, Start of Month, End of Month, Days in Month

🛠️ Step-by-Step Power Query Approach

Step 1: Load both tables into Power Query

  • Load your Employee data as Employees
  • Load or create a Calendar table as Calendar

Step 2: Create a Cross Join (Employee × Month)

// Add a key column with value 1 to both tables
EmployeesWithKey = Table.AddColumn(Employees, "Key", each 1),
CalendarWithKey = Table.AddColumn(Calendar, "Key", each 1),

// Merge the two tables on 'Key' to simulate a CROSS JOIN
CrossJoined = Table.NestedJoin(EmployeesWithKey, "Key", CalendarWithKey, "Key", "Calendar", JoinKind.Inner),
Expanded = Table.ExpandTableColumn(CrossJoined, "Calendar", {"Month", "MonthStart", "MonthEnd", "DaysInMonth"})

Step 3: Add FTE Logic (Human Version in M)

Now, for each row (i.e. employee × month), add a custom column to calculate the monthly FTE:

AddFTE = Table.AddColumn(Expanded, "MonthlyFTE", each
let
    startDate = [PositionStartDate],
    endDate = if [PositionEndDate] = null then #date(2099,12,31) else [PositionEndDate],
    monthStart = [MonthStart],
    monthEnd = [MonthEnd],
    fte = [FTE],
    daysInMonth = [DaysInMonth],
    status = [EmploymentStatus],
    leaveType = [SecondaryStatus],

    activeDays =
        if leaveType = "Seconded out to" or leaveType = "Transferred out to" then
            if startDate >= monthStart and startDate <= monthEnd and endDate > monthEnd then Duration.Days(monthEnd - startDate)
            else if endDate >= monthStart and endDate <= monthEnd then Duration.Days(endDate - monthStart)
            else if startDate >= monthEnd and [EmploymentStartDate] < monthStart then daysInMonth
            else if startDate < monthStart and endDate > monthEnd then daysInMonth
            else if startDate < monthStart and endDate >= monthStart and endDate <= monthEnd then Duration.Days(endDate - monthStart)
            else if endDate < monthStart then daysInMonth
            else null
        else if List.Contains({"Maternity Leave", "Adoption Leave", "Long Term Sick", "Sabbatical", "Parental Leave"}, leaveType) then
            0
        else
            if startDate > monthEnd or endDate < monthStart then 0
            else if startDate <= monthStart and (endDate >= monthEnd or endDate = null) then daysInMonth
            else if startDate > monthStart and endDate >= monthEnd then Duration.Days(monthEnd - startDate)
            else if startDate <= monthStart and endDate < monthEnd then Duration.Days(endDate - monthStart)
            else if startDate > monthStart and endDate < monthEnd then Duration.Days(endDate - startDate)
            else null,

    monthlyFTE = if activeDays = null then null else Number.Round((activeDays / daysInMonth) * fte, 2)

in
    monthlyFTE)

🧾 Final Step: Load into Power BI

Once calculated, this gives you a table with:

  • Employee ID
  • Month
  • Monthly FTE

You can then:

  • Use this for visuals (FTE by department/month/etc.)
  • Join to dimension tables (cost centre, manager, programme, etc.)

Leave a Comment