How to create DAX to group data by hours

Anonymous — Fri, 18 Aug 2023 07:26:01 GMT

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

Re: How to create DAX to group data by hours

barritown — Mon, 23 Oct 2023 09:33:40 GMT

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

topic How to create DAX to group data by hours in DAX Commands and Tips

How to create DAX to group data by hours

Re: How to create DAX to group data by hours