Find the difference in time after sorting by Service Order # and Time

GunnerJ — Fri, 07 Jun 2019 18:24:43 GMT

I'm needing to find out how long certian tasks are taking. I have the columns BI_EVENT_DT_TM, BI_SO_NBR, and BI_TASK_CD. There's also BI_WRKFLW_TASK_SEQ_NBR but tasks are not always completed in the correct order so the sequence number can't be used.

In order to get the needed results I need to have my dataset order by BI_SO_NBR so I'm just finding times for tasks within their respective service order, then order by BI_EVENT_DT_TM ascending. After they're ordered in the formula I then need to subtract the time between one line's BI_EVENT_DT_TM by the BI_EVENT_DT_TM of the task that was completed after it.

So if the a service order had 2 tasks in it one timed at 12:00:00 and the next at 12:00:10 I would get a value of 10 seconds or 00:00:10 for "TASK TIME" for the first task.

This code is close but not there yet. It returns the running sum of time for each Service order and the results are off by one row.

Column = VAR temp =

TOPN (

FILTER (

Query1,

Query1[BI_SO_NBR] = EARLIER (Query1[BI_SO_NBR])

&& Query1[BI_EVENT_DT_TM] < EARLIER (Query1[BI_EVENT_DT_TM] )

Query1[BI_SO_NBR], DESC, Query1[BI_EVENT_DT_TM],ASC

)

RETURN

DATEDIFF ( MINX ( temp, Query1[BI_EVENT_DT_TM] ), Query1[BI_EVENT_DT_TM], SECOND)

topic Find the difference in time after sorting by Service Order # and Time in DAX Commands and Tips

Find the difference in time after sorting by Service Order # and Time