Create a summary table from a primary table of incident timestamps

[MichaelHutchens]

Hi folks, I'm hoping someone can assist. I need to create a summary table from a primary table of incidents.

The primary table contains a list of incident start dates, and their duration in hours and minutes. Here's an example:

incident table

Date of incident start	Incident duration (HH:MM)
29/01/2021 8:10am	2:02
29/01/2021 4:15pm	0:30
5/2/2021 11:20am	1:15
20/02/2021 2:50pm	9:05
20/02/2021 2:40pm	0:05
21/02/2021 2:02pm	0:49
18/01/2021 8:32am	1:10

The secondary table should be a matrix that breaks down each incident into it's starting hour (based on a 24-hour clock), how many incidents started in that hour bracket, and the average duration of all of those incidents in each hour bracket. Here's an example, based on the sample data above:

Hour	Count of incidents	Average incident duration (HH:MM)
0	0	00:00
1	0	00:00
2	0	00:00
3	0	00:00
4	0	00:00
5	0	00:00
6	0	00:00
7	0	00:00
8	2	01:36
9	0	00:00
10	0	00:00
11	1	01:15
12	0	00:00
13	0	00:00
14	3	03:19
15	0	00:00
16	1	00:30
17	0	00:00
18	0	00:00
19	0	00:00
20	0	00:00
21	0	00:00
22	0	00:00
23	0	00:00

I need to show zeros rather than blanks, if there aren't any incidents in a particular hour bracket or time period (I'll be using a slicer on the summary table, using the 'date of incident start' column).

Any help would really be appreciated, thanks so much 🙂

[TomMartens · Accepted solution]

Hey @MichaelHutchens ,

first I transformed  sample data a little, as the Power BI data model necessary, does not know the data type duration.

My transformations in Power Query are

• Converted the column Date of Incident Start to Date/Time
• Added a column extracting the hour part from the Time of the start column
• Converted duration column into a time column
• Added a column of the data type duration derived from the duration column, this is necessary to do some time based maths, here I chose the transform "Total seconds"

The table will look like this:

 

Then I created these 2 measures:

count of incidents = 
var __NoOfRows = COUNTROWS( 'Table' )
return
IF( ISBLANK( __NoOfRows ) , 0 , __NoOfRows )

And this one, this one is lengthy but simple, here some time based calculations happens and also some formatting

average incident duration = 

    var __noofrows = [count of incidents]
    var __duration = CALCULATE( SUM( 'Table'[Total Seconds] ) )
    var __averageDurationInSeconds = DIVIDE( __duration , __noofrows , 0 )
    var constSecondsPerDay = 24 * 60 * 60
    var constSecondsPerHour = 60 * 60
    var __Days = TRUNC( DIVIDE( __averageDurationInSeconds , constSecondsPerDay ) , 0)
    var __Hours = TRUNC( DIVIDE( __averageDurationInSeconds - __Days * constSecondsPerDay , constSecondsPerHour ) , 0 )
    var __Minutes = TRUNC( DIVIDE( __averageDurationInSeconds - __Days * constSecondsPerDay - __Hours * constSecondsPerHour , 60 ) , 0 )
    var __Seconds = TRUNC( __averageDurationInSeconds - __Days * constSecondsPerDay - __Hours * constSecondsPerHour - __Minutes * 60 )
    // create HH:MM Format
    var __DaysFormatted = IF( __Days = 0 , "" , CONCATENATE(__Days , "." ) ) 
    var __HoursFormatted = CONCATENATE( IF( __Hours < 10 , RIGHT( CONCATENATE( "00" , FORMAT( __Hours , "" ) ) , 2 ) , __Hours ) , ":" )
    var __MinutesFormatted = CONCATENATE( IF( __Minutes < 10 , RIGHT( CONCATENATE( "00" , FORMAT( __Minutes , "" ) ) , 2 ) , __Minutes ) , ":" )
    var __SecondsFormatted = IF( __Seconds < 10 , RIGHT( CONCATENATE( "00" , __Seconds ) , 2 ) , __Seconds )

    return
        __DaysFormatted & __HoursFormatted & __MinutesFormatted & __SecondsFormatted

In addition to that I also create a simple table that reflects the 24hours of the day 0 .. 23, I created a relationship between both tables.

All of this allows to create a table visual like this:

Hopefully, this is what you are looking for and helps to tackle your challenge.

Regards,

Tom

[MichaelHutchens]

I'm always excited when I get a reply to my questions in this forum, and this response is an example why, @TomMartens . Thanks so much for your time, this is perfect 🙂