Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

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 Sequence 1 = Time elapsed for Sequence 2. 

 

The calculation below is returning the difference between the min and max times within a service order. I need to replace the min time with the max time of the previous sequence number. My problem is that I'm not great at focusing my calculation to a sequencial process. 

Task Time =
CALCULATE (
DATEDIFF (
MIN ( Workflow[BI_EVENT_DT_TM] ),
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"
)
)
 
Below is a sample of data. You can see that the Task Time is showing in seconds the difference between the times in each sequence. The lower time is the same for all sequences as it's just the start of the service order. My calculation is looking at that date/time in all my results. How can I alter the code to look at the previous max time? So for Sequence 2 it'd compare 3/14/2019 1:12:15 PM to 3/14/2019 1:12:14 for a result of 1 second (automated process) for Task time on Sequence 2. For sequence one it could just do the current calculation. 
Task Time DAX.PNG
 
Please let me know if any additional info is needed. 
@TedPattison  - Ted I am tagging you as I took your intro course and was hoping you might be able to assist in this. 
Thank you
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

ccc.png

 

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.

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @GunnerJ 

 

If you take the answer of someone, please mark it as the solution to help the other members find it more quickly. If not,  let me know and I'll try to help you further. 

Thanks.

 

Best Regards,

Allan

v-alq-msft
Community Support
Community Support

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:

ccc.png

 

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
Resident Rockstar
Resident Rockstar

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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.