topic Re: Find Order Duration based on Start Stop time in DAX Commands and Tips

Find Order Duration based on Start Stop time

Wirestat — Tue, 21 Feb 2023 05:15:29 GMT

Hello, please can you help to find order duration based on starting and finishing time 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: Find Order Duration based on Start Stop time

Anonymous — Wed, 22 Feb 2023 02:41:31 GMT

Hi Wirestat ,

Here are the steps you can follow：

1. . In Power Query -- Create Indices for the two tables -- Add Column – Index Column – From 1.

2. Create calculated column.

Rank = RANKX( FILTER(ALL('Table'),'Table'[Order id]=EARLIER('Table'[Order id])),[Index],,ASC)

Flag = VAR _mindate = MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) && 'Table'[Rank] = MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) ), [Rank] ) ), [From] ) VAR _maxdate = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) && 'Table'[Rank] = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Order id] = EARLIER ( 'Table'[Order id] ) ), [Rank] ) ), [To] ) VAR _datediff = DATEDIFF ( _mindate, _maxdate, MINUTE ) RETURN IF ( 'Table'[Order id] = 0, BLANK (), IF ( VALUE ( LEFT ( _mindate, FIND ( ":", _mindate ) - 1 ) ) < VALUE ( LEFT ( _maxdate, FIND ( ":", _maxdate ) - 1 ) ), _datediff + 24 * 60, _datediff ) )

Time = var _hour= QUOTIENT('Table'[Flag],60) var _minute= MOD('Table'[Flag],60) return IF( [Flag]=BLANK(),BLANK(), IF( 'Table'[Rank]= MAXX(FILTER(ALL('Table'),'Table'[Order id]=EARLIER('Table'[Order id])),[Rank]), TIME( _hour,_minute,0),BLANK()))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Find Order Duration based on Start Stop time

Wirestat — Wed, 22 Feb 2023 09:55:55 GMT

Hello Anonymous

Thank you but i get an error

Re: Find Order Duration based on Start Stop time

tamerj1 — Wed, 22 Feb 2023 10:09:51 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 ) )