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:
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.
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.
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:
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}}),
/* Step 9: Group to get MaxEndDate per UserID and FTE_Week */
_GroupedMaxEndDate = Table.Group(
_ChangeType,
{"UserID", "FTE_Week"},
{{"MaxEndDate", each List.Max([FTE_Week_EndDate]), type date}}
),
/* Step 10: Add FTE Week Month Name (MMM) */
_FTE_WK_Month = Table.AddColumn(
_GroupedMaxEndDate,
"FTE_WK_Month",
each Date.ToText([MaxEndDate], "MMM"),
type text
),
/* Step 11: Merge back to original ChangeType to keep all columns */
_Merge_LT = Table.NestedJoin(
_ChangeType,
{"UserID", "FTE_Week"},
_FTE_WK_Month,
{"UserID", "FTE_Week"},
"MonthLookup",
JoinKind.LeftOuter
),
_ExpandedMonth = Table.ExpandTableColumn(_Merge_LT, "MonthLookup", {"FTE_WK_Month"}),
/* Step 12: Add FTE Week Calendar Month */
_FTE_WK_Cal_Month = Table.AddColumn(_ExpandedMonth, "FTE_WK_CalMonth", each Date.ToText([FTE_Week_EndDate],"MMM"), type text)
/* Storing the Metadata of required steps */
meta [ref_WeeklyTable = _FinalTable]
in
_FTE_WK_Cal_Month
Result:
The output of the above Query is as follows.
Notes:
The FTE_WK_CalMonth is nothing but the calendar Month of the FTE Week. The FTE_WK_Month returns the actual Month of the FTE Week.--------------------------------------------------------------------------------------------------------
We can use the below DAX Logic to identify which week has Split and return 'M_Split' for the Week that got split and overlaps between two months, 'Y_Split' for the Week that got Split and overlaps between two Years.
FTE_Week_Split_Type =
VAR MinStartDate =
CALCULATE (
MIN ( tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week_StartDate] ),
ALLEXCEPT ( tbl_FTE_Weekly_Alloc_Stats_UnPivot,
tbl_FTE_Weekly_Alloc_Stats_UnPivot[UserID],
tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week]
)
)
VAR MaxEndDate =
CALCULATE (
MAX ( tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week_EndDate] ),
ALLEXCEPT ( tbl_FTE_Weekly_Alloc_Stats_UnPivot,
tbl_FTE_Weekly_Alloc_Stats_UnPivot[UserID],
tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week]
)
)
RETURN
SWITCH (
TRUE(),
YEAR ( MinStartDate ) <> YEAR ( MaxEndDate ), "Y_Split",
MONTH ( MinStartDate ) <> MONTH ( MaxEndDate ), "M_Split",
"No_Split"
)
To align the FTE Month (FTE_WK_Month) with the FTE Week rather than the calendar month, you can use the DAX logic below if the field hasn't already been created in Power Query.
For Example:
WK18 (W1-May) has split as [28-04-2025 to 30-04-2025] and [01-05-2025 to 02-05-2025], where first 3 days falls in April Month and next 2 days fall into May Month.
Since the FTE Week is classified as WK1-May, even though the Week begins in April, the FTE Week Month should be recorded as May.
FTE_WK_Month =
VAR MaxEndDate =
CALCULATE (
MAX ( tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week_EndDate] ),
ALLEXCEPT ( tbl_FTE_Weekly_Alloc_Stats_UnPivot,
tbl_FTE_Weekly_Alloc_Stats_UnPivot[UserID],
tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week] )
)
RETURN
FORMAT ( MaxEndDate, "MMM" )
Next, we can return the Week of the FTE_Month as 'W1-Jan-2025' using below logic:
Week_FTE_Month =
VAR MaxEndDate =
CALCULATE (
MAX ( tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week_EndDate] ),
ALLEXCEPT (
tbl_FTE_Weekly_Alloc_Stats_UnPivot,
tbl_FTE_Weekly_Alloc_Stats_UnPivot[UserID],
tbl_FTE_Weekly_Alloc_Stats_UnPivot[FTE_Week]
)
)
VAR FirstOfMonth = DATE ( YEAR ( MaxEndDate ), MONTH ( MaxEndDate ), 1 )
VAR WeekNumInMonth =
INT ( ( DAY ( MaxEndDate ) + WEEKDAY ( FirstOfMonth, 2 ) - 1 ) / 7 ) + 1
RETURN
"W" & WeekNumInMonth & "-" & FORMAT ( MaxEndDate, "MMM-yyyy" )
Result:
The result of the above 3 measures is as follows:
--------------------------------------------------------------------------------------------------------
Thanks
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.