Task Times. Finding Time between events after sorts

[GunnerJ]

I need to find the time between two tasks to determine how long each individual task takes. To be successful my code needs to only compare rows of the same Service order number, then be sorted by EVENT_DT_TM asc so that I can compare tasks as they happened cronologically, finally values are sorted by sequence number. The sequence number is the suggested order but not always followed. I use it as a tie breaker if the EVENT_DT_TMs are exactly alike. Some tasks are automated which results in the same times.

Currently my code is not subtracting values from the row before it but instead finding the difference between itself and the first record with the same service order number. Also if a sequence number is out of order the calculation is returning a zero which is incorrect.

See picture below. The 3rd and 4th lines show times of 26 and 47 seconds. The 3rd row shows the correct value (only because the 2nd row is the same as the 1st) but the 4th line should show 11 seconds since thats the difference between the 3rd and 4th row.

Here's my code at this point.

Task Time  = VAR temp =

TOPN (

1,

FILTER (

Workflow,

Workflow[BI_SO_NBR] = EARLIER (Workflow[BI_SO_NBR])

&& Workflow[BI_EVENT_DT_TM] <= EARLIER (Workflow[BI_EVENT_DT_TM] )

&& Workflow[BI_WRKFLW_TASK_SEQ_NBR] > EARLIER(Workflow[BI_WRKFLW_TASK_SEQ_NBR])

),

Workflow[BI_SO_NBR], DESC, Workflow[BI_EVENT_DT_TM],ASC, Workflow[BI_WRKFLW_TASK_SEQ_NBR], ASC

)

RETURN

IF(ISEMPTY(temp), 0, DATEDIFF ( Workflow[BI_EVENT_DT_TM], MINX ( temp,Workflow[BI_EVENT_DT_TM] ), SECOND)) * -1

[v-lili6-msft]

HI, @GunnerJ 

1. Why the first line and second line sorted column "BI_WRKFLW_TASK_SEQ_NBR" are "21" and "22"?

Shouldn't it be "1" and  "2"?

And for this your formula conditional will always return the first line data.

&& Workflow[BI_WRKFLW_TASK_SEQ_NBR] > EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )

You may try this formula

Task Time = 
VAR temp =
    TOPN (
        1,
        FILTER (
            Workflow,
            Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
                && Workflow[BI_EVENT_DT_TM] < EARLIER ( Workflow[BI_EVENT_DT_TM] )
              
        ),
        Workflow[BI_SO_NBR], DESC,
        Workflow[BI_EVENT_DT_TM], DESC,
        Workflow[BI_WRKFLW_TASK_SEQ_NBR], DESC
    )
RETURN
    IF (
        ISEMPTY ( temp ),
        0,
        DATEDIFF (
            Workflow[BI_EVENT_DT_TM],
            MINX ( temp, Workflow[BI_EVENT_DT_TM] ),
            SECOND
        )
    ) * -1

Result:

By the way for the 4th line should show 11 21 seconds since thats the difference between the 3rd and 4th row

Best Regards,

Lin

[GunnerJ]

@v-lili6-msft 

Thank you very much for your feedback. After reviewing the code with my data it is seemingly very close to being complete. I put in your code and the task times on my previos data seem to be working as needed. To answer your quesiton about the BI_WRKFLOW_TASK_SEQ_NBR they are more of suggestions than actual requirements. For the most part they are followed but like in this example some of them are completed out of order. 

 Previous data but with new code

I looked through some other service orders just to see if all situations were covered and came accross this. So in this scenario the time for line 2 would be correct but every other line should be 0 seconds. However, I beleive the code is comparing each line to the first line since all of the following lines have the same BI_EVENT_DT_TM. In the case that the event time is the same I would like the BI_WRKFLW_TASK_SEQ_NBR to sort them. Hopefully the end result would give all of the lines after the 2nd line 0 for their task time. (I've used the title "TEST TIME" but my final solution will be "task time") 

If this issue is resolved then I believe that the code will be complete and the code can be accepted as a solution. 

Thank you so much for taking a look at my code! I'm somewhat new to Dax and this has really been a struggle for me but I'm hoping to learn from the community. Please let me know if you are able to further assist or need anything else from me.

[v-lili6-msft · Accepted solution]

HI, @GunnerJ 

If so, try this formula

Task Time = 
VAR temp =
    TOPN (
        1,
        FILTER (
            Workflow,
            Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
                && Workflow[BI_WRKFLW_TASK_SEQ_NBR] < EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
              
        ),
        Workflow[BI_SO_NBR], DESC,
        Workflow[BI_EVENT_DT_TM], DESC,
        Workflow[BI_WRKFLW_TASK_SEQ_NBR], DESC
    )
RETURN
    IF (
        ISEMPTY ( temp ),
        0,
        DATEDIFF (
            Workflow[BI_EVENT_DT_TM],
            MINX ( temp, Workflow[BI_EVENT_DT_TM] ),
            SECOND
        )
    ) * -1

Result:

Best Regards,

Lin

[GunnerJ]