Forum Discussion

GunnerJ's avatar
GunnerJ
Post Patron
6 years ago
Solved

Time between sequences

My calculation is looking at the completion time compared to the start of the service order but I need it to look at the prior completion time. So comp time for Task Sequence 2 - Comp Time for Task S...
Need Help
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, GunnerJ 

    Based on your description, i create data to reproduce your scenario. You can create a calculated column in data table as follows:

     

    TASK TIME =

    VAR seq = Workflow[BI_WRKFLW_TASK_SEQ_NBR]

    VAR minDT =

        CALCULATE (

            Max ( Workflow[BI_EVENT_DT_TM] ),

            FILTER (

                 Workflow ,

                Workflow[BI_WRKFLW_TASK_SEQ_NBR]

                    = CALCULATE (

                        MAX ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] ),

                        FILTER ( Workflow , Workflow[BI_WRKFLW_TASK_SEQ_NBR] < seq && Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )

                        && Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )

                        && Workflow[BI_WORK_EVENT_CD] = "COMP")

                    )

            )

        )

        return

                CALCULATE (

                DATEDIFF (

                minDT,

                MAX ( Workflow[BI_EVENT_DT_TM] ),

                SECOND

                ),

                FILTER (

                ALL ( Workflow ),

                Workflow[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )

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

                && Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )

                && Workflow[BI_WORK_EVENT_CD] = "COMP"

                )

                )

     

    Result:

     

    If I misunderstand the your thoughts, please show us the sample data and  expected output. I am glad to solve the problem for you.

     

    Best Regards,

    Allan

     

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

JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

The logic you are asking for is faulty on task 5 and 6. The second line in both tasks is not in chronological order and the difference between them would be a negative number. What would you want the result to be in that case? And what do you want the result to be in the base of a 'first occurence'  of a task (so rownumber 1, 3, 5, 7 etc)?

I am thinking something allong the lines of if the task number occurence is the first (ranked by EVENT_DT_TM) then return something, else calculate the datediff between current row and the row where tasknumber < currentRowTasknumber and date = MAX(date). 

Would love to figure this one out for you, could you please copy paste the table structure and sample data as tekst into a reply? Then I could copy it into power bi and create a solution for you (provided you can anser the first questions 🙂 ).

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂