cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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 Results = CALCULATE(
DATEDIFF(StartDate,EndDate,DAY),

RETURN
Results

5 REPLIES 5
Community Support

You can refer to the following measure

``````Outgoing Wire Elased Time =
VAR _requ =
MINX (
FILTER (
[OrderNumber] IN VALUES ( 'hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber] )
),
)
VAR _rece =
MAXX (
FILTER (
[OrderNumber] IN VALUES ( 'hsoa cvw_DashboardSSCTasks_HSOA'[OrderNumber] )
),
)
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.

Frequent Visitor

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.

Community Support

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

Best Regards!

Yolo Zhu

Frequent Visitor

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

Community Support

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])
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.