How to build a Weekly FTE Allocation Logic using Power Query

How to split Weeks and Allocate FTE Hours in Power BI Using M-Code

In workforce planning and project tracking, allocating Full-Time Equivalent (FTE) hours across calendar weeks is a common challenge, especially when weeks span across months or years. 

This article walks through a Power Query solution that transforms raw allocation data into a week-by-week breakdown and handling tricky cases like weeks that span across months or years.

Scenario:

Lets say there is a table of Employee(FTE) allocations table, with details like User_ID (FTE_ID), UserName (FTE_Name),Department, Country, Project_ID, FTE_Start_Date, FTE_End_Date,FTE_Alloc_Status(Full-Time or Part-Time) and FTE_Percentage. 

Conditions to Consider:

• The FTE_Percentage values are: 1 , 0.5, 0.25 etc.
• If the FTE_Percentage =1 means FTE is Full-Time allocated to a Project with FTE Daily Hours as 8.
• If the FTE_Percentage < 1(Eg. 0.5) means FTE is Part-Time allocated to a Project with FTE Daily Hours as FTE_Percentage * 8 ( E.g: 0.5*8 = 4 Hours)
• The default Weekly FTE Days are 5 and FTE Hours are 5*8 = 40 Hours, these hours may get adjusted when there is Week split during month change.
• The FTE Allocation to the respective Project Starts from FTE_Start_Date and ends at FTE_End_Date, and the allocation of Hours for that FTE Weeks needs to distribute accordingly based on FTE_Percentage.

Weekly Structure and Splitting Logic:

Weeks are generated from the first Monday of Week1 of the Year, and each week starts from Monday to Friday. Each week is represented by a Week start and end date, and indexed as WK1_StartDate_EndDate, WK2_StartDate_EndDate, etc.

However, if a week crosses a month or year boundary, it's split into two segments, and the FTE Hours will be allocated according to the Split of Days of that Week:

WK_A: Covers the portion of the week within the starting month

WK_B: Covers the remainder in the next month

Week split at Month-Year Change:

WK1_A_2024-12-30_2024-12-31 (2024 December portion) = 2 Days (2*8 = 16 Hours)

WK1_B_2025-01-01_2025-01-03 (2025 January portion) = 3 Days (3*8 = 24 Hours)

Week split at Month Change:

WK40_A_2025-09-29_2025-09-30: (2025 September portion) = 2 Days (2*8 = 16 Hours)

WK40_B_2025-10-01_2025-10-03: (2025 October portion) = 3 Days (3*8 = 24 Hours)

Example:
Now we can achieve the above Scenario using the following M-Query shown in the below example.

The sample Input Data as follows:

The complete Power M-Query Logic to fill the above FTE Allocation Scenario:

let

/* Step 1: Create raw allocation table */

_RawAllocationTable = #table(

        {"UserID", "UserName", "Department", "Country", "Project_ID", "FTE_Start_Date", "FTE_End_Date", "FTE_Alloc_Status", "FTE_Percent"},

        {

            {123, "Alpha", "Finance", "Canada", "Proj_654", #date(2024, 12, 30), #date(2025, 3, 31), "Full-Time", 1},

            {234, "Beta", "HR", "France", "Proj_543", #date(2025, 1, 1), #date(2025, 6, 30), "Part-Time", 0.5},

            {345, "Gamma", "IT", "India", "Proj_765", #date(2025, 1, 1), #date(2025, 9, 30), "Full-Time", 1},

            {456, "Delta", "Marketing", "Switzerland", "Proj_876", #date(2024, 12, 30), #date(2025, 12, 31), "Part-Time", 0.5},

            {567, "Epsilon", "Operations", "England", "Proj_987", #date(2025, 1, 1), #date(2025, 6, 30), "Full-Time", 1}

        }

    ),

/* Step 2: Add FTE_Day_Hours as FTE_Percent × 8 */

_AllocationTable = Table.AddColumn(

        _RawAllocationTable,

        "FTE_Day_Hours",

        each [FTE_Percent] * 8,

        type number

    ),

/* Step 3: Set year and first Monday */

    _CurrentYear = 2025,

    _Jan1 = #date(_CurrentYear, 1, 1),

    _FirstWeekMonday = Date.AddDays(_Jan1, -Date.DayOfWeek(_Jan1, Day.Monday)),

/* Step 4: Generate list of Mondays until next Year Jan Week1 */

_WeekStartDates = List.Generate(

        () => _FirstWeekMonday,

        each _ < #date(_CurrentYear + 1, 1, 1),

        each Date.AddDays(_, 7)

    ),

/* Step 5: Build week column records with split logic */

_WeekColumnRecords = List.Transform(

        List.Zip({

            _WeekStartDates,

            List.Transform(_WeekStartDates, each Date.AddDays(_, 4)),

            List.Numbers(1, List.Count(_WeekStartDates))

        }),

        each 

            let

                _StartDate = _{0},

                _EndDate = _{1},

                _WeekIndex = _{2},

                _StartMonth = Date.Month(_StartDate),

                _EndMonth = Date.Month(_EndDate),

                _StartYear = Date.Year(_StartDate),

                _EndYear = Date.Year(_EndDate),

                _IsSplit = _StartMonth <> _EndMonth or _StartYear <> _EndYear,

                _Split1End = #date(_StartYear, _StartMonth, Date.DaysInMonth(_StartDate)),

                _Split2Start = Date.AddDays(_Split1End, 1),

                _Split1Days = Duration.Days(_Split1End - _StartDate) + 1,

                _Split2Days = Duration.Days(_EndDate - _Split2Start) + 1,

                _ColName1 = "WK" & Text.From(_WeekIndex) & "_A_" & Date.ToText(_StartDate, "yyyy-MM-dd") & "_" & Date.ToText(_Split1End, "yyyy-MM-dd"),

                _ColName2 = "WK" & Text.From(_WeekIndex) & "_B_" & Date.ToText(_Split2Start, "yyyy-MM-dd") & "_" & Date.ToText(_EndDate, "yyyy-MM-dd"),

                _Result = if _IsSplit then {

                    [ColumnName = _ColName1, StartDate = _StartDate, EndDate = _Split1End, Days = _Split1Days],

                    [ColumnName = _ColName2, StartDate = _Split2Start, EndDate = _EndDate, Days = _Split2Days]

                } else {

                    [ColumnName = "WK" & Text.From(_WeekIndex) & "_A_" & Date.ToText(_StartDate, "yyyy-MM-dd") & "_" & Date.ToText(_EndDate, "yyyy-MM-dd"), StartDate = _StartDate, EndDate = _EndDate, Days = 5]

                }

            in

                _Result

    ),

/* Step 6: Flatten list of records */

_FlatColumns = List.Combine(

        List.Transform(_WeekColumnRecords, each if _ is list then _ else {_})

    ),

/* Step 7: Add week columns with calculated hours based on FTE range */

_AddWeeks = Table.TransformRows(

        _AllocationTable,

        each 

            let

                _Row = _,

                _DailyHours = _Row[FTE_Day_Hours],

                _FTEStart = _Row[FTE_Start_Date],

                _FTEEnd = _Row[FTE_End_Date],

                _WeekAllocations = Record.FromList(

                    List.Transform(_FlatColumns, each 

                        let

                            _WeekStart = _[StartDate],

                            _WeekEnd = _[EndDate],

                            _Days = _[Days],

                            _ColName = _[ColumnName],

                            _IsInRange = (_WeekStart >= _FTEStart and _WeekEnd <= _FTEEnd),

                            _Hours = if _IsInRange then _Days * _DailyHours else 0 /* null */

                        in

                            _Hours

                    ),

                    List.Transform(_FlatColumns, each _[ColumnName])

                )

            in

                Record.Combine({_Row, _WeekAllocations})

    ),

/* Step 8: Convert records back to table format */

_FinalTable = Table.FromRecords(_AddWeeks),

  

Unpivot_Wkly_Alloc = Table.UnpivotOtherColumns(_FinalTable, {"UserID", "UserName", "Department", "Country","Project_ID","FTE_Alloc_Status", "FTE_Percent", "FTE_Day_Hours","FTE_Start_Date","FTE_End_Date"}, "Attribute", "Value"),

    RenameColumns1 = Table.RenameColumns(Unpivot_Wkly_Alloc,{{"Attribute", "FTE_Week_Range"}, {"Value", "FTE_Weekly_Hours"}}),

    

Split_FTE_Week_by_Delimiter = Table.SplitColumn(RenameColumns1, "FTE_Week_Range", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"FTE_Week_Range.1", "FTE_Week_Range.2", "FTE_Week_Range.3", "FTE_Week_Range.4"}),

    

RenameColumns2 = Table.RenameColumns(Split_FTE_Week_by_Delimiter,{{"FTE_Week_Range.1", "FTE_Week"}, {"FTE_Week_Range.3", "FTE_Week_StartDate"}, {"FTE_Week_Range.4", "FTE_Week_EndDate"}}),

    

RemoveColumns = Table.RemoveColumns(RenameColumns2,{"FTE_Week_Range.2"}),

    _WeekSortID = Table.AddColumn(RemoveColumns, "Week_SortID", each Number.From(Text.Middle([FTE_Week], 2, Text.Length([FTE_Week]) - 2))),

    

ChangeType = Table.TransformColumnTypes(_WeekSortID,{{"UserID", Int64.Type}, {"UserName", type text}, {"Department", type text}, {"Country", type text}, {"Project_ID", type text}, {"FTE_Alloc_Status", type text}, {"FTE_Percent", Int64.Type}, {"FTE_Start_Date", type date}, {"FTE_End_Date", type date}, {"FTE_Day_Hours", Int64.Type}, {"FTE_Week", type text}, {"FTE_Week_StartDate", type date}, {"FTE_Week_EndDate", type date}, {"FTE_Weekly_Hours", Int64.Type}, {"Week_SortID", Int64.Type}})

in

    ChangeType

Result:

The output of the above Query is as follows.

Notes:
Now we can enable relationship between Date Table and FTE Allocation table using either FTE Week StartDate or FTE Week EndDate for further analysis by Week, Month and Year.

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------