Rolling Average Total by Hour

[deplanche]

I am looking for a way to calculate a rolling total for a consecutive 48 hour period that is by hour.  Below is some sample data from the list I have where I have the start date and time, end date and time, total hours, rate, and total billed for each employee.   The employees did not necessarily work the same shifts or the same number of hours, and the number of people working ay any time varied.  What I am trying to find is when the most dollars were spend for labor during a 48 hour period.  The period does NOT need to align with the days, so it can be from 11/19 at 8 AM to 11/21 at 8 AM for example, but want to look at every possible 48 hour period during that time to see what the maximum and what the cost was relative to other times.  Everything I can seem to find on this only goes to the day/date increments, but need it for hourly.

Thanks

Date              ID               Rate                   Hrs.                       Total Bill             Start Date/Time                   End Date/Time

11/18/2022	15-0626	$             250.00	10.50	$         2,625.00	November 18, 2022 10:00:00 AM	November 18, 2022 8:30:00 PM
11/18/2022	15-0626	$             250.00	1.00	$             250.00	November 18, 2022 9:00:00 AM	November 18, 2022 10:00:00 AM
11/18/2022	15-0627	$             250.00	12.00	$         3,000.00	November 18, 2022 8:30:00 PM	November 19, 2022 8:30:00 AM
11/19/2022	15-0628	$             250.00	12.00	$         3,000.00	November 19, 2022 8:30:00 AM	November 19, 2022 8:30:00 PM
11/19/2022	15-0629	$             250.00	12.00	$         3,000.00	November 19, 2022 8:30:00 PM	November 20, 2022 8:30:00 AM
11/20/2022	15-0630	$             250.00	12.00	$         3,000.00	November 20, 2022 8:30:00 AM	November 20, 2022 8:30:00 PM
11/20/2022	15-0631	$             250.00	12.00	$         3,000.00	November 20, 2022 8:30:00 PM	November 21, 2022 8:30:00 AM
11/21/2022	15-0632	$             250.00	12.00	$         3,000.00	November 21, 2022 8:30:00 AM	November 21, 2022 8:30:00 PM
11/21/2022	15-0633	$             250.00	12.00	$         3,000.00	November 21, 2022 8:30:00 PM	November 22, 2022 8:30:00 AM
11/22/2022	15-0634	$             250.00	12.00	$         3,000.00	November 22, 2022 8:30:00 AM	November 22, 2022 8:30:00 PM
11/22/2022	15-0635	$             250.00	12.00	$         3,000.00	November 22, 2022 8:30:00 PM	November 23, 2022 8:30:00 AM
11/23/2022	15-0636	$             250.00	8.50	$         2,125.00	November 23, 2022 8:30:00 AM	November 23, 2022 5:00:00 PM
11/26/2022	15-0637	$             250.00	12.00	$         3,000.00	November 26, 2022 8:30:00 AM	November 26, 2022 8:30:00 PM
11/26/2022	15-0638	$             250.00	11.00	$         2,750.00	November 26, 2022 8:30:00 PM	November 27, 2022 7:30:00 AM
11/26/2022	15-0638	$             250.00	1.00	$             250.00	November 26, 2022 7:30:00 AM	November 26, 2022 8:30:00 AM
11/18/2022	2154	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2156	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2157	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2155	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2160	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2158	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM
11/18/2022	2159	$             275.00	16.00	$         4,400.00	November 18, 2022 12:00:00 PM	November 19, 2022 4:00:00 AM

[lbendlin · Accepted solution]

You need it for halfhourly since your start and end times do not always align to hours.

There are a couple of options how to tackle this, one of the simpler ones is to have a disconnected calendar table with your required granularity

let
    Source = {Number.From(Date.From("2022-11-18"))..Number.From(Date.From("2022-11-27"))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {0..47}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each [Column1]+[Custom]/48),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}, {"Column1", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Custom.1", "Slot"}})
in
    #"Renamed Columns"

Then you can create a measure that computes the charges per half hour slot per ID

and then you can refine that to properly handle the rollup to hours and days.

Based on that measure you can then compute your 48 hrs rolling average as needed.