How to create DAX to group data by hours

[Anonymous]

Hi all,

May need your help on an issue i am facing right now. Currently i have a table consisting starttime, endtime and status of  a machine. I have successfully get the running time using this data . However, i would like to group this data by hours. This means i would like to see the status "running" on each of the hour how much it is running.  This is the table of data that i currently have and i want a table like this which are group properly by hours (only filtered on running ) 

However, since the duration is based on start time of the status , some of the data is not what i expected. You can see at 12PM the hour is more than 1 hour. How do i assign / create a measure properly as each hour should have only 1 hour duration and if its exceed then can add to the next running duration by hour.  

Below is the sample data :

time START	time END	Status	Next Status	Duration (Min)
16/08/2023 12:01	16/08/2023 12:02	Stop	Stop	0
16/08/2023 12:02	16/08/2023 12:02	Stop	Stop	0
16/08/2023 12:02	16/08/2023 12:06	Running	Stop	5
16/08/2023 12:02	16/08/2023 12:02	Stop	Running	0
16/08/2023 12:06	16/08/2023 12:08	Stop	Stop	2
16/08/2023 12:08	16/08/2023 12:08	Stop	Running	0
16/08/2023 12:08	16/08/2023 12:08	Running	Stop	0
16/08/2023 12:08	16/08/2023 12:08	Stop	Running	0
16/08/2023 12:08	16/08/2023 12:09	Running	Stop	1
16/08/2023 12:09	16/08/2023 12:10	Stop	Running	1
16/08/2023 12:10	16/08/2023 12:10	Running	Stop	0
16/08/2023 12:10	16/08/2023 12:10	Stop	Running	0
16/08/2023 12:10	16/08/2023 12:10	Running	Stop	0
16/08/2023 12:10	16/08/2023 12:22	Stop	Running	12
16/08/2023 12:22	16/08/2023 12:22	Running	Stop	0
16/08/2023 12:22	16/08/2023 12:22	Stop	Running	0
16/08/2023 12:22	16/08/2023 12:22	Running	Stop	0
16/08/2023 12:22	16/08/2023 12:22	Stop	Running	0
16/08/2023 12:22	16/08/2023 12:23	Running	Stop	1
16/08/2023 12:23	16/08/2023 12:24	Stop	Stop	1
16/08/2023 12:24	16/08/2023 12:29	Running	Stop	5
16/08/2023 12:24	16/08/2023 12:24	Stop	Running	0
16/08/2023 12:29	16/08/2023 12:29	Stop	Stop	1
16/08/2023 12:29	16/08/2023 12:29	Stop	Running	0
16/08/2023 12:29	16/08/2023 12:29	Running	Stop	0
16/08/2023 12:29	16/08/2023 12:29	Stop	Running	0
16/08/2023 12:29	16/08/2023 12:30	Running	Running	1
16/08/2023 12:30	16/08/2023 12:40	Running	Stop	9
16/08/2023 12:40	16/08/2023 12:50	Stop	Running	10
16/08/2023 12:50	16/08/2023 12:50	Running	Stop	0
16/08/2023 12:50	16/08/2023 12:50	Stop	Running	0
16/08/2023 12:50	16/08/2023 13:56	Running	Stop	66
16/08/2023 13:56	16/08/2023 13:57	Stop	Running	1
16/08/2023 13:57	16/08/2023 13:57	Running	Stop	1
16/08/2023 13:57	16/08/2023 13:57	Stop	Running	0
16/08/2023 13:57	16/08/2023 13:59	Running	Stop	1
16/08/2023 13:59	16/08/2023 14:11	Stop	Stop	12

Thanks and Regards,

Hairul

[barritown]

Hi @Anonymous,

I can propose the following solution.

Step 1. You generate a table with the rounded hours.

Table = GENERATESERIES ( MROUND ( MIN ( Data[time START] ), TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 ), 
                          MROUND ( MAX ( Data[time END] ), TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 ), 
                          TIME ( 1, 0, 0 ) )

2) Then you add a calculated column like this:

In plain text:

Column = 
VAR PeriodStart = [Value]
VAR PeriodEnd = PeriodStart + TIME ( 1, 0, 0 )
VAR _tbl1 = FILTER ( Data, [Status] = "Running" )
VAR _tbl2 = FILTER ( _tbl1,
                    ( [time START] >= PeriodStart && [time START] < PeriodEnd ) ||
                    ( [time START] < PeriodEnd && [time END] >= PeriodStart ) )
VAR _tbl3 = ADDCOLUMNS ( _tbl2, 
                         "Calculated Duration",
                         VAR TimeDiff = MIN ( PeriodEnd, [time END] ) - MAX ( PeriodStart, [time START] )
                         RETURN HOUR ( TimeDiff) * 60 + MINUTE ( TimeDiff ) )
RETURN SUMX ( _tbl3, [Calculated Duration] )

I've added an extra line to your dataset to test how the calculation works for periods exceeding 1 hour (PBIX is in the attachment). 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian