Elapsed Time Between Two Dates With Different Rows showing muliplte key values

Question

Good morning,&nbsp;I need to calculate the elapsed time between two dates that is found on two separate rows. Each row has an ID column that will tie the two rows together as well as task names that will be used to find the start and stop time.&nbsp;Each order number will appear twice, once for each wire task that is being completed. I need to find the elapsed time, excluding weekends, from the start of the initial wire task (task requested date) to the completion of the wire approval task (task received date&nbsp;&nbsp;&nbsp;I researched what I could and based on what I found, this seemed like the best approach but still getting an error.&nbsp;Outgoing Wire Elased Time = VAR StartTask = CALCULATE(SELECTEDVALUE('hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber]),'hsoa cvw_DashboardSSCTasks_HSOA'[TaskName] = "HSoA Outgoing Wire")VAR StopTask = CALCULATE(SELECTEDVALUE('hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber]),'hsoa cvw_DashboardSSCTasks_HSOA'[TaskName] = "HSoA Outgoing Wire Approval")VAR StartDate = CALCULATE(SELECTEDVALUE('hsoa cvw_DashboardSSCTasks_HSOA'[TaskRequestedDate]),'hsoa cvw_DashboardSSCTasks_HSOA'[TaskName] = "HSoA Outgoing Wire")VAR EndDate = CALCULATE(SELECTEDVALUE('hsoa cvw_DashboardSSCTasks_HSOA'[TaskReceivedDate]),'hsoa cvw_DashboardSSCTasks_HSOA'[TaskName] = "HSoA Outgoing Wire Approval")VAR Results = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF(StartDate,EndDate,DAY),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StartTask = StopTask)&nbsp; &nbsp; RETURN&nbsp; &nbsp; Results&nbsp;

anonymous · Answer

Hi&nbsp;Nicky_The_Knows&nbsp;
You can refer to the following measure
Outgoing Wire Elased Time =
VAR _requ =
    MINX (
        FILTER (
            ALLSELECTED ( 'hsoa cvw_DashboardSSCTasks_HSOA' ),
            [OrderNumber] IN VALUES ( 'hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber] )
        ),
        [TaskRequestedDate]
    )
VAR _rece =
    MAXX (
        FILTER (
            ALLSELECTED ( 'hsoa cvw_DashboardSSCTasks_HSOA' ),
            [OrderNumber] IN VALUES ( 'hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber] )
        ),
        [TaskRequestedDate]
    )
RETURN
    DATEDIFF ( _requ, _rece, SECOND )

&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

nicky_the_knows · Answer

Morning Yolo&nbsp;I tried the solution above but it showing the same value for all rows. It looks like it is grabbing the first start time of the first order number and the stop time of the last order number and then applying that to all orders in the model.

anonymous · Answer

Hi&nbsp;Nicky_The_Knows&nbsp;
Can you provide some more sample data and the output you want?
&nbsp;
Best Regards!
Yolo Zhu
&nbsp;
&nbsp;
&nbsp;

nicky_the_knows · Answer

Good afternoon,&nbsp;I have removed the columns from the model that are not applicable and included multiples.&nbsp;For each order number, there will be two tasks that need to be completed, outgoing wire and outgoing wire approval. I want to capture the time it takes from the beginning of the process (outgoing wire) to the end of the process (outgoing wire approval) for each order.&nbsp;In the sample below, I have 5 distinct order numbers where I need to capture this value for each order number seperatly. I have included table where the data can be copied as well as an image with highlighted cells showing the order number groups with the start and stop times.&nbsp;OrderNumberTaskNameTaskRequestedDateTaskReceivedDate2194703HSoA: Outgoing Wire10/6/23 8:50 PM10/6/23 9:04 PM2194703HSoA: Outgoing Wire Approval10/6/23 9:04 PM10/6/23 9:54 PM2196838HSoA: Outgoing Wire9/29/23 3:22 PM9/29/23 3:22 PM2196838HSoA: Outgoing Wire Approval9/29/23 6:43 PM9/29/23 7:05 PM2204868HSoA: Outgoing Wire10/10/23 7:15 PM10/10/23 7:15 PM2204868HSoA: Outgoing Wire Approval10/10/23 7:15 PM10/10/23 7:41 PM2206811HSoA: Outgoing Wire9/29/23 12:49 PM9/29/23 12:55 PM2206811HSoA: Outgoing Wire Approval9/29/23 12:55 PM9/29/23 12:55 PM2207317HSoA: Outgoing Wire10/4/23 4:20 PM10/4/23 4:20 PM2207317HSoA: Outgoing Wire Approval10/4/23 4:20 PM10/4/23 4:52 PM&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Nicky_The_Knows&nbsp;
Try the following measure
Outgoing Wire Elased Time = var _requ=MINX(FILTER(ALLSELECTED('hsoa cvw_DashboardSSCTasks_HSOA'),[OrderNumber] in VALUES('hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber])),[TaskRequestedDate])
var _rece=MAXX(FILTER(ALLSELECTED('hsoa cvw_DashboardSSCTasks_HSOA'),[OrderNumber] in VALUES('hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber])),[TaskReceivedDate])
RETURN DATEDIFF(_requ,_rece,SECOND)

Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

&nbsp;

Forum Discussion

Elapsed Time Between Two Dates With Different Rows showing muliplte key values

5 Replies

Related content

Different schema into different destination

Calculate Elapsed Time for Different Statuses

Calculate elapsed time

Calculate Elapsed Time

Business Days Elapsed for each month.