Rollup of Daily Deliveries by Total Time and Total Occurrences

[Madmaxigail]

I am looking for help on obtaining data on how long each day I have multiple deliveries out at the same time.  I have an Incident #, Start DateTime and End DateTime (amongst many other columns of data that are note revelent for this particular question).  My times are hh:mm:ss.  Lets say the data looks like this:

Incident #	Start DateTime	End DateTime
123	January 1, 2023 07:00:00	January 1, 2023 07:30:00
125	January 1, 2023 07:00:00	January 1, 2023 07:15:00
132	January 1, 2023 07:20:00	January 1, 2023 08:00:00
135	January 1, 2023 08:30:00	January 1, 2023 08:55:00
140	January 1, 2023 08:35:00	January 1, 2023 09:00:00
141	January 1, 2023 08:50:00	January 1, 2023 09:10:00

So, from 07:00:00 to 07:15:00 I had 2 calls at once. 

From 07:15:00 to 07:20:00 I had 1 call happening. 

From 07:20:00 to 07:30:00 I had 2 calls at once.

From 07:30:00 to 08:00:00 I had 1 call happening. 

From 08:00:00 to 08:00:00 I had 0 calls happening.

From 08:30:00 to 08:35:00 I had 1 call happening.

From 08:35:00 to 08:50:00 I had 2 calls happening.

From 08:50:00 to 08:55:00 I had 3 calls happening.

From 08:55:00 to 09:00:00 I had 2 calls happening.

From 09:00:00 to 09:10:00 I had 1 call happening.

(I think I got that right, lol).  Is there a way to roll this info up in a manner that would tell me how many times and for how long I had 0 calls, 1 call, 2 calls, 3 calls, 4 calls, or 5 calls at a time for EACH DAY?  There would only be a max of 5 calls at a time.

Hoping to generate a table like this for total # of times:

Date	1 Call	2 Calls	3 Calls	4 Calls	5 Calls
January 1, 2023	4	4	1	0	0

and a table like this for total time (in hh:mm:ss):

Date	1 Call	2 Calls	3 Calls	4 Calls	5 Calls
January 1, 2023	00:50:00	00:45:00	00:05:00	00:00:00	00:00:00

[Ashish_Mathur]

Hi,

The problem i see there is that the time buckets are not consistent.  In some buckets, the gap is 5 minutes while in others it is 15 minutes.  How does one/software dicide the buckets which need to be built?

[Madmaxigail]

Would you have any suggestions on where I can search for a solution?  Maybe I'm overcomplicating things because I have to think others would like to have similar information.  If I could summarize, what I really need to know is how many times I have 4 deliveries happening at the same time and a total of for how long.  I would need this on a day by day basis.  All I have is 4 trucks so I am trying to find out many times I am at full capacity.

[Ashish_Mathur]

Hi,

To be able to come up with any solution, i think we will have to create time buckets.

[Madmaxigail]

Thanks for your suggestion.  Ok, I am thinking 5 or 10 minute buckets could be OK.  I am not a coder by trade but can follow directions pretty well.  What is the preferred method to do this?  Are there any walkthroughs available to do this?

[Ashish_Mathur]

Sorry but i cannot think of a way to do this.

[Madmaxigail]

Hello,

Thank for your reply. In short there are no consistent buckets. I did perfrom a workaround in Excel using 15 minute timeframe buckets and using an "if" formula to populate the the bucket (column) with a "1" if the bucket timeframe was between the start and end time of the delivery.  I could easily click and drag to create the 96 15 minute buckets for a day.  Then easily copy the "if" formula across all columns and rows.  I was hoping there may be a way to get more granular using PowerBI (plus the ability to utilize filters from other datapoints I have in my data).  

[v-stephen-msft · Accepted solution]

Hi  @Madmaxigail , 

At first I thought your time interval was fixed, at 15 minute intervals, so I tried to make the following two columns using M code.

let
    Source = List.Dates(#date(2023, 1, 1), 1, #duration(1, 1, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TimeColumn", each List.Times(#time(7, 0, 0), 9, #duration(0, 0, 15, 0))),
    #"Expanded TimeColumn1" = Table.ExpandListColumn(#"Added Custom", "TimeColumn"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded TimeColumn1", {{"Column1", type text}, {"TimeColumn", type text}}, "en-US"),{"Column1", "TimeColumn"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Range(
   #"Merged Columns"[DateTime],
   [Index]+1,
   1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values",{{"DateTime", "Start"}, {"Custom", "End"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1)
in
    #"Removed Bottom Rows"

But since your time interval is not regular, I can only rely on enter data to build the table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDBDYAgDIXhVQhnE1qaRuDmAE5A2H8No2JCeXj98x1eW6vnwCFSFLcXInecfhsT653aVjEaFwnZ04yShZJJpT7DsITbfpygE10wnZXCtt6swn98cXB5tS3jDbnw69oF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Main Table"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Incident #", "Start DateTime", "End DateTime"}, {"Incident #", "Start DateTime", "End DateTime"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Custom", each if [Start]>=[Start DateTime] and [End]<=[End DateTime] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Start", "End"}, {{"Count", each List.Sum([Custom]), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.From([Count])&" Call"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"End", "Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Start", List.Count)
in
    #"Pivoted Column"

You can download the attchment for details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Madmaxigail]

Hello Again Stephen,

I've used your proposed solution and I believe most of what you suggested worked.  I had to combine a couple other theories as well but I think you got it.  Thanks for your suggestion. 

[Madmaxigail]

Thanks Stephen. I think you have grasped my concept and yes in the real data my time is not constant and actually goes down to the second.  And I think that is part of my problem. I was able to use 15 minute time intervals in Excel to break down how many deliveries were happening in at least part of that 15 minute period.  But that is not accurate because if any part of that delivery occured within that 15 minute block it would count.  So if a delivery started at 08:14, it counted as occuring between 08:00 and 08:15.  I wonder if this data were displayed in a Gantt chart type format with the ability to roll up an active tracker whereby every time a delivery begins there is a +1 and every time a delivery ends it notes a -1 it would be the start of where I'm trying to go.  

I've been using PowerBi for some time now but I have no formal training, thus my question on this complex topic.  I think this is a question of correlation of data over date/time and duration.

In the end I would like to be able to know for every day of the year, how many times there were 2, 3, 4 or 5 deliveries at the same time and the overall daily duration of 2, 3, 4 or 5 deliveries at the same time. 

[Idrissshatila]

Hello,

if every row of data which is every incedent is a call then you count the incedent column and display is by date and you could see the number of calls .the following is a dax measure:

number of calls  = countrows('Table'[Incident#])

 and to get the duration of each call,you should add a calculated column in the table you referenced up here to show the duration in monutes for each row and it would have this measure:

Duration= datediff(Start DateTime, End DateTime, MINUTE)  

If I answered your question, please mark my post as solution, Appreciate your Kudos👍

Follow me on Linkedin

[Madmaxigail]

Thanks for your response, Idrissshatila.  I understand what you are suggesting and I think that is the start to solving my issue but I think my problem is a little more complex.  I need those counts and durations to correlate over multiple rows of data based upon date/times.