Forum Discussion
Split duration into hourly basis and also split dates when the process overflows the next day
Hi All,
I have a problem where in need to split the start and end time in hourly basis and find the duration for which the machine was busy, below is the sample data which i have. Preferably need this is in power query
| Key | Start Time | End Time | Machine Name |
| x1 | 2020-01-16T19:30:00.00Z | 2020-01-16T19:33:00.000Z | M1 |
| x2 | 2020-01-16T19:45:00.00Z | 2020-01-16T19:50:00.000Z | M1 |
| x3 | 2020-01-17T22:45:00.00Z | 2020-01-17T23:05:00.000Z | M2 |
| x4 | 2020-01-17T23:55:00.00Z | 2020-01-18T00:15:00.000Z | M2 |
| x5 | 2020-01-16T12:45:00.00Z | 2020-01-16T12:55:00.000Z | M3 |
I want to be able to split the data per hour of the day to find the duration within that hour the machine is busy, below is the sample output i am expecting from which i could calculate the sum/avg duration for which a machine was busy during the hour of the day, also maybe particular day of week. I tried this link but was not able to get the split in duration.
| Key | Start Time | End Time | Machine Name | Date | Hour | Duration Busy (mins) |
| x1 | 2020-01-16T19:30:00.00Z | 2020-01-16T19:33:00.000Z | M1 | 2020-01-16 | 19 | 3 |
| x2 | 2020-01-16T19:45:00.00Z | 2020-01-16T19:50:00.000Z | M1 | 2020-01-16 | 19 | 5 |
| x3 | 2020-01-17T22:45:00.00Z | 2020-01-17T23:05:00.000Z | M2 | 2020-01-17 | 22 | 15 |
| x3 | 2020-01-17T22:45:00.00Z | 2020-01-17T23:05:00.000Z | M2 | 2020-01-17 | 23 | 5 |
| x4 | 2020-01-17T23:55:00.00Z | 2020-01-18T00:15:00.000Z | M2 | 2020-01-17 | 23 | 5 |
| x4 | 2020-01-17T23:55:00.00Z | 2020-01-18T00:15:00.000Z | M2 | 2020-01-18 | 0 | 15 |
| x5 | 2020-01-16T12:45:00.00Z | 2020-01-16T12:55:00.000Z | M3 | 2020-01-17 | 12 | 10 |
Any help it terms of data manipulation and visualization (summarized hourly view/ week of the day view) to get the desired result of showing the period during which each machine is busy and available would be greatly appreciated.
For now I have visualized the data as attached but I need a summarized view and don't think this is the best way to visualize it.
Thanks in advance
Dan
9 Replies
- amitchandak
Super User
Dan_SP , This how I have done in past. Might need to try if this approach did not work.
I created an hour table and and then create a new table with help from that
Generateseries(1:24)
add column
hour = time([Value],0,0)
Then follow the same approach in the file(attached after signature) that I have for date. You need to get the hour from this new table to get diff
Hi amitchandak , I tried your suggestions but was not able to get it work, not sure if I did it correctly as your thing is for calculating the day counts. I need to explore still the dax queries.
v-henryk-mstfCommunity Support
Hi Dan_SP ,
After reading your question, I am a little confused. I want to know what the calculation logic is in the screenshot below. The same result cannot be obtained with End-Start in the marked lines. I hope you can provide detailed data information and expected results to facilitate my further testing.
Best Regards,
HenryHi v-henryk-mstf ,
PFA screenshot with new start and end time columns which I added for your reference, basically I won't it to split into hourly duration and find run time within that hour.
So if you see for key x3 its start time is 2020-01-17T22:45:00 and end time is 2020-01-17T23:05:00 so I would want to split the data to show that the machine M2 has run for 15mins in the 22nd hour and for 5 mins in the 23rd hour, thats the part I am looking for. Hope it answers your question.
Regards,
Dan
- v-henryk-mstf
Hi Dan_SP ,
Thank you for the accurate data and information you provided me in your reply. In this regard, I have done a test as a reference: first use append in Power Query to merge the two tables, and create two measures to find the duration and hours that are met under different conditions. Create a slicer measure to filter out different rows of hour, and get the following results:Hour_Reuslt = VAR cur_index = MIN ( 'Table'[Index] ) VAR cur__S_hour = SUM ( 'Table'[S_Hour] ) VAR cur_E_hour = SUM ( 'Table'[E_Hour] ) VAR next_index = CALCULATE ( SUM ( 'Table'[Index] ), FILTER ( ALL ( 'Table' ), 'Table'[Key] = MAX ( 'Table'[Key] ) && 'Table'[Index] > cur_index ) ) RETURN IF ( cur__S_hour == cur_E_hour, SUM('Table'[S_Hour]), IF ( SUM ( 'Table'[Index] ) < next_index, cur__S_hour, cur_E_hour) )Min_Reuslt = VAR cur_index = MIN ( 'Table'[Index] ) VAR cur__S_hour = SUM ( 'Table'[S_Hour] ) VAR cur_E_hour = SUM ( 'Table'[E_Hour] ) VAR next_index = CALCULATE ( SUM ( 'Table'[Index] ), FILTER ( ALL ( 'Table' ), 'Table'[Key] = MAX ( 'Table'[Key] ) && 'Table'[Index] > cur_index ) ) RETURN IF ( cur__S_hour == cur_E_hour, SUM ( 'Table'[E_Min] ) - SUM ( 'Table'[S_Min] ), IF ( SUM ( 'Table'[Index] ) < next_index, 60 - SUM ( 'Table'[S_Min] ), SUM ( 'Table'[E_Min] ) ) )Slicer_ = VAR cur_index = MIN ( 'Table'[Index] ) VAR cur__S_hour = SUM ( 'Table'[S_Hour] ) VAR cur_E_hour = SUM ( 'Table'[E_Hour] ) VAR next_index = CALCULATE ( SUM ( 'Table'[Index] ), FILTER ( ALL ( 'Table' ), 'Table'[Key] = MAX ( 'Table'[Key] ) && 'Table'[Index] > cur_index ) ) RETURN IF(cur__S_hour==cur_E_hour && cur_index <next_index,1,0)
If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
