Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to create DAX to group data by hours

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 ) 

 

Hairul_Khumaini_0-1692343454846.png

 

 

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 STARTtime ENDStatus Next StatusDuration (Min)
16/08/2023 12:0116/08/2023 12:02StopStop0
16/08/2023 12:0216/08/2023 12:02StopStop0
16/08/2023 12:0216/08/2023 12:06RunningStop5
16/08/2023 12:0216/08/2023 12:02StopRunning0
16/08/2023 12:0616/08/2023 12:08StopStop2
16/08/2023 12:0816/08/2023 12:08StopRunning0
16/08/2023 12:0816/08/2023 12:08RunningStop0
16/08/2023 12:0816/08/2023 12:08StopRunning0
16/08/2023 12:0816/08/2023 12:09RunningStop1
16/08/2023 12:0916/08/2023 12:10StopRunning1
16/08/2023 12:1016/08/2023 12:10RunningStop0
16/08/2023 12:1016/08/2023 12:10StopRunning0
16/08/2023 12:1016/08/2023 12:10RunningStop0
16/08/2023 12:1016/08/2023 12:22StopRunning12
16/08/2023 12:2216/08/2023 12:22RunningStop0
16/08/2023 12:2216/08/2023 12:22StopRunning0
16/08/2023 12:2216/08/2023 12:22RunningStop0
16/08/2023 12:2216/08/2023 12:22StopRunning0
16/08/2023 12:2216/08/2023 12:23RunningStop1
16/08/2023 12:2316/08/2023 12:24StopStop1
16/08/2023 12:2416/08/2023 12:29RunningStop5
16/08/2023 12:2416/08/2023 12:24StopRunning0
16/08/2023 12:2916/08/2023 12:29StopStop1
16/08/2023 12:2916/08/2023 12:29StopRunning0
16/08/2023 12:2916/08/2023 12:29RunningStop0
16/08/2023 12:2916/08/2023 12:29StopRunning0
16/08/2023 12:2916/08/2023 12:30RunningRunning1
16/08/2023 12:3016/08/2023 12:40RunningStop9
16/08/2023 12:4016/08/2023 12:50StopRunning10
16/08/2023 12:5016/08/2023 12:50RunningStop0
16/08/2023 12:5016/08/2023 12:50StopRunning0
16/08/2023 12:5016/08/2023 13:56RunningStop66
16/08/2023 13:5616/08/2023 13:57StopRunning1
16/08/2023 13:5716/08/2023 13:57RunningStop1
16/08/2023 13:5716/08/2023 13:57StopRunning0
16/08/2023 13:5716/08/2023 13:59RunningStop1
16/08/2023 13:5916/08/2023 14:11StopStop12

 

Thanks and Regards,

Hairul

 

1 REPLY 1
barritown
Super User
Super User

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:

barritown_0-1698053396167.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors