Hi All,
I have a head scratcher that has been bugging me for almost a week now. I have a simple table (StaffPlan):
WBS - Work Breakdown Structure ID where employee charges time,
Employee Name - employee's name,
Employee Title - employee's job title,
Craft - employee's discipline (Piping, Boilermaker, Project Management, etc.),
Shift - work shift (day / night),
In Est - Yes / No - Was Employee in original estimate,
Start Work - date employee started work,
End Work - date employee will finish work,
Days Per Week - days per week employee will be working (4, 5, 6, or 7 are the usual values),
Hours Per Day - hours per day the employee will be working (8, 10, 11, 12, or 13 are usual values),
ETC Hours - Estimate to Complete Hours. On a daily basis, this will be the same as Hours Per Day. (This field is in the table as a check to be sure the M Code generates the correct result. It will be removed when testing is completed.),
OT Factor - W2 Rate multiplyer. Will be 1.0 or 1.5 in most cases.,
W2 Rate - Employee's W2 wage rate.
The ultimate goal is to calculate the total Straight-Time hours and Over-Time Hours given the Start Date and End Date and generate a proper data set with days by employee and hour per day.
I have all of this done, except the part that's giving me fits is calculating the Straight-Time hours for the first week. The employee may work 5 days a week, 10 hours per day, or 7 days a week for 13 hours a day and start employment on any day of the week. So the first week's hours will only be Over-Time if they hit 40 hours during the week. The code if have so far works, as long as an employee starts work on Monday (Our work week is Monday (0) thru Sunday (6)). Here's the M Code:
let
Source = Excel.CurrentWorkbook(){[Name="StaffPlan"]}[Content],
ChangeDataTypes = Table.TransformColumnTypes(Source,{{"WBS", type text}, {"Employee Name", type text}, {"Employee Title", type text}, {"Shift", type text}, {"Start Work", type date}, {"End Work", type date}, {"Days Per Week", Int64.Type}, {"Hours Per Day", Int64.Type}, {"ETC Hours", type number}, {"OT Factor", type number}, {"W2 Rate", Currency.Type}, {"Craft", type text}, {"In Est", type text}}),
AddField_StartDOW = Table.AddColumn(ChangeDataTypes, "Start DOW", each Date.DayOfWeek([Start Work], Day.Monday), Int64.Type),
AddField_Index = Table.AddIndexColumn(AddField_StartDOW, "Index", 1, 1),
AddField_EID = Table.AddColumn(AddField_Index, "EID", each Text.PadStart(Text.From([Index]),8,"0"), type text),
RemoveIndex = Table.RemoveColumns(AddField_EID,{"Index"}),
AddField_ListOfDates = Table.AddColumn(RemoveIndex, "ListOfDates", each List.Transform({Number.From([Start Work])..Number.From([End Work])}, each Date.From(_))),
ReorderColumns = Table.ReorderColumns(AddField_ListOfDates,{"ListOfDates", "WBS", "Craft", "EID", "Employee Name", "Employee Title", "Shift", "Start Work", "Start DOW", "End Work", "Days Per Week", "Hours Per Day", "ETC Hours", "OT Factor", "W2 Rate"}),
Expand_ListOfDates = Table.ExpandListColumn(ReorderColumns, "ListOfDates"),
RenamedColumns = Table.RenameColumns(Expand_ListOfDates,{{"ListOfDates", "Work Date"}}),
AddField_DayOfWeek = Table.AddColumn(RenamedColumns, "Day Of Week", each Date.DayOfWeek([Work Date], Day.Monday), Int64.Type),
AddField_WorkDays = Table.AddColumn(AddField_DayOfWeek, "WorkDays", each if [Day Of Week]<=([Days Per Week]-1) then "Work Day" else "Non-Work Day", type text),
RemoveHPDFromNonWorkDay = Table.ReplaceValue(AddField_WorkDays,each [Hours Per Day], each if [WorkDays] = "Non-Work Day" then 0 else [Hours Per Day],Replacer.ReplaceValue,{"Hours Per Day"}),
ChangeType = Table.TransformColumnTypes(RemoveHPDFromNonWorkDay,{{"Work Date", type date}, {"Hours Per Day", type number}}),
AddField_ETCHours = Table.AddColumn(ChangeType, "Estimate To Complete Hours", each if [WorkDays]="Work Day" then [Hours Per Day] else 0, type number),
SortOn_EmployeeAndWorkDate = Table.Sort(AddField_ETCHours,{{"EID", Order.Ascending}, {"Work Date", Order.Ascending}}),
//This only works if employee starts on Monday. 😞
AddField_STHours = Table.AddColumn(SortOn_EmployeeAndWorkDate, "Straight-Time Hours", each
if [WorkDays] = "Work Day" then
if ([Day Of Week]+1) * [Hours Per Day] <= 40 then [Hours Per Day] else
if (([Day Of Week]+1) * [Hours Per Day]) - 40 >= [Hours Per Day] then 0 else ([Hours Per Day] + 40)-(([Day Of Week] + 1) * [Hours Per Day])
else 0, type number),
AddField_OTHours = Table.AddColumn(AddField_STHours, "Over-Time Hours", each if [WorkDays] = "Work Day" then [Hours Per Day]-[#"Straight-Time Hours"] else 0, type number),
AddField_STCost = Table.AddColumn(AddField_OTHours, "Estimate to Complete Straight-Time Cost", each ((1 + BdnRate) * [W2 Rate]) * [#"Straight-Time Hours"], Currency.Type),
AddField_OTCost = Table.AddColumn(AddField_STCost, "Estimate to Complete Over-Time Cost", each ((1 + BdnRate) * [W2 Rate] * [OT Factor]) * [#"Over-Time Hours"], Currency.Type),
AddField_ETCCost = Table.AddColumn(AddField_OTCost, "Estimate To Complete Cost", each [#"Estimate to Complete Straight-Time Cost"] + [#"Estimate to Complete Over-Time Cost"], Currency.Type),
RemoveHelperColumns = Table.RemoveColumns(AddField_ETCCost,{"Start Work", "End Work", "Days Per Week", "Hours Per Day", "ETC Hours", "OT Factor", "W2 Rate", "Day Of Week"}),
ReorderAllColumns = Table.ReorderColumns(RemoveHelperColumns,{"WBS", "Craft", "Employee Title", "Employee Name", "Shift", "Work Date", "WorkDays", "Straight-Time Hours", "Over-Time Hours", "Estimate To Complete Hours", "Estimate to Complete Straight-Time Cost", "Estimate to Complete Over-Time Cost"}),
Filter_ETCDates = Table.SelectRows(ReorderAllColumns, each [Work Date] > WEDate)
in
Filter_ETCDates
Here's a link to a workbook (I hope):
Staff Plan Workbook
Thanks in advance...
John
