Date difference with sorting criteria

[GunnerJ]

I need to find the time it takes to complete a task in a given service order. It'll effectively look to see if the Service Order Number is the same then look at all Sequence Numbers that have the same value and subtract the latest EVENT_DATE_TM from the earliest EVENT_DATE_TM to determine how long that Sequence number took to complete. This would only look at rows where "BI_WORK_EVENT_CD = "COMP"

*This code is similar to what I need and I think it is a good starting point but I'm having trouble converting it to the new set of criteria.*

Test 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

Here is a look at some sample data. As an example, when looking at lines 2,3, and 4 you can see the BI_SO_NBRs are the same as are the BI_WRKFLW_TASK_SEQ_NBRs. The formula would return the difference between lines 4 and 2 since they are the largest date and smallest date of that SO_NBR and SEQ_NBR subset.

If anyone is able to help I would be greatly appreciative of your efforts. I'm still learning Dax but am trying to complete what seems to be a more difficult task.

Thank you!

[dax]

Hi GunnerJ,

Did you want to get result like below?

If so, you could try to use below expression to see whether it works or not(you could replace the “day” to “second” in datediff function)

Column 2 =
CALCULATE (
    DATEDIFF (
        MIN ( Workflow[BI_EVENT_DT_TM] ),
        MAX ( Workflow[BI_EVENT_DT_TM] ),
        DAY
    ),
    FILTER (
        ALL ( Workflow ),
        Workflow[BI_WRKFLW_TASK_SED_NBR] = EARLIER ( Workflow[BI_WRKFLW_TASK_SED_NBR] )
            && Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
            && Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
    )
)

Best Regards,

Zoe Zhi

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

[GunnerJ]

@dax Thank you very much for the response. Your code does show me the correct times from the largest to smallest date.

 

I do have one question if you don't mind taking a look. When looking at the average time for task times I'm not seeing what I'd expect. The result I'd be looking for would be 121 seconds as that'd be the average between 122 and 120 seconds, but this shows 91.6 seconds for some reason.

I also was thinking if the values stay as they are I still might not get the exact average since an average would be looking at 5 values instead of just 2. Is there a way that you know of where the code can bring back the correct value but only for one row and leave the other's blank? That way it's able to caculate the true average.

Again thank you so much for your response you've gotten me SOOO much closer to my desired end result!