Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |