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

[Nicky_The_Knows]

Good morning,

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.

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

I researched what I could and based on what I found, this seemed like the best approach but still getting an error.

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(

                DATEDIFF(StartDate,EndDate,DAY),

                StartTask = StopTask)

RETURN

    Results

[v-xinruzhu-msft]

Hi @Nicky_The_Knows 

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 )

Best Regards!

Yolo Zhu

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

[Nicky_The_Knows]

Morning Yolo

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.

[v-xinruzhu-msft]

Hi @Nicky_The_Knows 

Can you provide some more sample data and the output you want?

Best Regards!

Yolo Zhu

[Nicky_The_Knows]

Good afternoon,

I have removed the columns from the model that are not applicable and included multiples.

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.

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.

OrderNumber	TaskName	TaskRequestedDate	TaskReceivedDate
2194703	HSoA: Outgoing Wire	10/6/23 8:50 PM	10/6/23 9:04 PM
2194703	HSoA: Outgoing Wire Approval	10/6/23 9:04 PM	10/6/23 9:54 PM
2196838	HSoA: Outgoing Wire	9/29/23 3:22 PM	9/29/23 3:22 PM
2196838	HSoA: Outgoing Wire Approval	9/29/23 6:43 PM	9/29/23 7:05 PM
2204868	HSoA: Outgoing Wire	10/10/23 7:15 PM	10/10/23 7:15 PM
2204868	HSoA: Outgoing Wire Approval	10/10/23 7:15 PM	10/10/23 7:41 PM
2206811	HSoA: Outgoing Wire	9/29/23 12:49 PM	9/29/23 12:55 PM
2206811	HSoA: Outgoing Wire Approval	9/29/23 12:55 PM	9/29/23 12:55 PM
2207317	HSoA: Outgoing Wire	10/4/23 4:20 PM	10/4/23 4:20 PM
2207317	HSoA: Outgoing Wire Approval	10/4/23 4:20 PM	10/4/23 4:52 PM

[v-xinruzhu-msft]

Hi @Nicky_The_Knows 

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 helps, then please consider Accept it as the solution to help the other members find it more quickly.