Trying to find monthly total based on week start date and week end date

[Anonymous]

I have a timesheet calculator report which has the week start date and week end date columns. And it has Mon to Sun filled hours for each employee for that week.

Now how do I calculate the mothly total based on this?
Below are columns. Now how do I get the monthly total for a single employee? Since the weeks will overlap, need to get the time for the start of the month to ti

Employee Name	week start date	Week End Date	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Total
Employee1	3/28/2022	4/3/2022	8	8	8	8	8			40
Empoyee2	4/4/2022	4/10/2022	8	8	8	8	8			40

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE01VNJRMtY3stA3MjAyArJN9I1hTAssGIJMDJRidcBmgIyAaDNBmGBoQIoRcGfgMAOCsJhibITpCiNTmDZTfUMCrgBRJhZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Week Start Date" = _t, #"Week End Date" = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t, Sun = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Week Start Date", type date}, {"Week End Date", type date}, {"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}, {"Total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({List.Transform(List.Dates([Week Start Date],Duration.Days([Week End Date]-[Week Start Date])+1,#duration(1,0,0,0)), each Date.ToText(_,"yyyy-MMM")),Record.ToList(Record.SelectFields(_,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}))})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Week Start Date", "Week End Date", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Total"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Expanded Custom", "Custom"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom1", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Year-Month", each if Number.Mod([Index],2)=0 then [Custom] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hours", each if Number.Mod([Index],2)=0 then #"Added Custom1"[Custom]{[Index]+1} else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([#"Year-Month"] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Employee Name", "Year-Month"}, {{"Total Monthly Hours", each List.Sum([Hours]), type nullable number}})
in
    #"Grouped Rows"

[jennratten]

This is how you can calculate the hours per month.  You will have to separate the hours for each month.  You can do that by renaming the columns for the days of the week to be the number of days offset from the start date, then unpivot those columns.  Then you will be abe to add the days offset to the start date to get the actual date for the day of the week; add a column for the start of the month and finally, you will be able to aggregate the hours for each individual month.

SNIP

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE01VNJRMtY3stA3MjAyArJN9I1hTAssWAGKTQyUYnXApoAMgWg0QZhhaECsIbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"week start date" = _t, #"Week End Date" = _t, Mon = _t, Tue = _t, Wed = _t, Thur = _t, Fri = _t, Sat = _t, Sun = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"week start date", type date}, {"Week End Date", type date}, {"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thur", Int64.Type}, {"Fri", Int64.Type}, {"Sat", type text}, {"Sun", type text}, {"Total", Int64.Type}}),
    RenameColumns = Table.RenameColumns(#"Changed Type",List.Zip({{"Mon", "Tue", "Wed", "Thur", "Fri", "Sat", "Sun"},{"0".."6"}})),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(RenameColumns, {"0".."6"}, "DaysOffset", "Hours"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Only Selected Columns",{{"DaysOffset", Int64.Type}, {"Hours", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each Date.AddDays([week start date],[DaysOffset]), Date.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Custom", "Start of Month", each Date.StartOfMonth([Date]), type date)
in
    #"Inserted Start of Month"

[Anonymous]

Thank you. That worked wonders. Since other solution was created first I had accepted it. Appreciate your input!

[jennratten]

No problem at all - I'm glad everything worked out.