Forum Discussion
How to Find Orders Duration based on Start Stop time
Hello, how its possible to find order duration based on starting and finishing time, for example like in column duration.
| Order id | From | To | Duration |
| 0 | 23:00 | 23:03 | |
| 1306993 | 23:03 | 23:03 | |
| 1306993 | 23:03 | 23:06 | |
| 1306993 | 23:22 | 23:33 | |
| 1306993 | 23:33 | 23:33 | |
| 1306993 | 23:34 | 23:35 | |
| 1306993 | 23:35 | 23:37 | |
| 1306993 | 23:38 | 23:39 | |
| 1306993 | 23:42 | 23:43 | |
| 1306993 | 23:43 | 23:43 | 00:40 |
| 0 | 23:43 | 23:44 | |
| 1305300 | 23:44 | 23:45 | |
| 1305300 | 23:45 | 23:46 | |
| 1305300 | 23:47 | 23:47 | |
| 1305300 | 23:48 | 23:49 | |
| 1305300 | 23:54 | 23:54 | |
| 1305300 | 23:54 | 23:55 | |
| 1305300 | 23:56 | 23:59 | |
| 1305300 | 23:59 | 00:00 | |
| 1305300 | 00:00 | 00:01 | |
| 1305300 | 00:02 | 00:03 | |
| 1305300 | 00:03 | 00:03 | |
| 1305300 | 00:04 | 00:04 | |
| 1305300 | 00:04 | 00:07 | |
| 1305300 | 00:07 | 00:07 | |
| 1305300 | 00:07 | 00:08 | |
| 1305300 | 00:08 | 00:09 | 00:25 |
| 0 | 00:09 | 00:10 | |
| 1307012 | 00:53 | 00:54 | |
| 1307012 | 00:54 | 00:55 | |
| 1307012 | 00:56 | 00:58 | |
| 1307012 | 01:02 | 01:05 | 00:12 |
| 0 | 01:05 | 01:05 | |
| 1307031 | 01:05 | 01:05 | |
| 1307031 | 01:06 | 01:06 | |
| 1307031 | 01:11 | 01:11 | |
| 1307031 | 01:13 | 01:14 | |
| 1307031 | 01:19 | 01:42 | |
| 1307031 | 01:44 | 01:45 | |
| 1307031 | 01:45 | 01:46 | |
| 1307031 | 01:46 | 01:46 | 00:41 |
Hi Wirestat
Please tryDuration (Minutes) = SUMX ( VALUES ( 'Table'[Order ID] ), VAR CurrentIDTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) ) VAR FirstStart = MINX ( CurrentIDTable, 'Table'[From] ) VAR LastEnd = MAXX ( CurrentIDTable, 'Table'[To] ) RETURN DATEDIFF ( FirstStart, LastEnd, MINUTE ) )
7 Replies
Hi.
Use DateDifference function to achieve the same requirement.Minutes taken = DATEDIFF(tablename[From], tablename[To], MINUTE)Though this will just give the minutes.
To achieve the exact requirement, you can follow the Solution mentioned in this below link:
Solved: datediff in HH:MM format - Microsoft Power BI Community
OR
Aggregating Duration/Time - Microsoft Power BI Community
Thank you.
If my answer helps you, please mark it as solution.Hi VishalJhaveri, unfortunatly this answer provide diffrance for each row, and my goal is to find duration for complete order. Thank your for affort!
Hello tamerj1, the gaps shows sub orders serving, just ignore them, the goal to display duration of order from 23:03 until 23:43 which is 40 min time for order 1306993
- tamerj1
Community Champion
Hi Wirestat
Please tryDuration (Minutes) = SUMX ( VALUES ( 'Table'[Order ID] ), VAR CurrentIDTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) ) VAR FirstStart = MINX ( CurrentIDTable, 'Table'[From] ) VAR LastEnd = MAXX ( CurrentIDTable, 'Table'[To] ) RETURN DATEDIFF ( FirstStart, LastEnd, MINUTE ) )
FreemanZSuper User
hi Wirestat
try to plot a table visual with the order id column and a measure like:
Duration = MAXX( TableName, TableName[To] ) - MINX( TableName, TableName[From] )it worked like:
your from and to columns shall include date info, with date info, you shall get the correct duration for 1305300 as well.
