topic Re: How to Find Orders Duration based on Start Stop time in DAX Commands and Tips

How to Find Orders Duration based on Start Stop time

Wirestat — Tue, 21 Feb 2023 09:26:25 GMT

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

Re: How to Find Orders Duration based on Start Stop time

Anonymous — Tue, 21 Feb 2023 09:42:20 GMT

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.

Re: How to Find Orders Duration based on Start Stop time

tamerj1 — Tue, 21 Feb 2023 09:34:33 GMT

Hi Wirestat

what about the gaps?

Re: How to Find Orders Duration based on Start Stop time

Wirestat — Tue, 21 Feb 2023 09:35:09 GMT

Hi VishalJhaveri, unfortunatly this answer provide diffrance for each row, and my goal is to find duration for complete order. Thank your for affort!

Re: How to Find Orders Duration based on Start Stop time

Wirestat — Tue, 21 Feb 2023 09:39:53 GMT

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

Re: How to Find Orders Duration based on Start Stop time

FreemanZ — Tue, 21 Feb 2023 09:47:09 GMT

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.

Re: How to Find Orders Duration based on Start Stop time

tamerj1 — Tue, 21 Feb 2023 12:33:05 GMT

Hi Wirestat
Please try

Duration (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 ) )

Re: How to Find Orders Duration based on Start Stop time

Wirestat — Tue, 21 Feb 2023 14:00:56 GMT

Thank you sir, its working!