How to Calculate time saved with overlapping time

[Anonymous]

I'm having a devil of time to figure out how to calculate the time difference between one scan and multiple overlapping scans. See example below.

ProjectId	ScanId	ScanId.1	StartedDate	CompletedDate	StartedDated.1	CompletedDate.1	Seconds	Seconds2
100	5	7	8/04/2021 12:05:16 PM	8/04/2021 12:30:29 PM	8/04/2021 12:04:45 PM	8/04/2021 12:22:10 PM	1513	1045
100	5	9	8/04/2021 12:05:16 PM	8/04/2021 12:30:29 PM	8/04/2021 12:25:06 PM	8/04/2021 12:50:30 PM	1513	1524

• ScanId is the "original" scan
• ScanId.1 is the "overlapping" scan
• The first set of date/time fields relate to the "original" scan
• The second set of date/time fields relate to the "overlapping" scan
• You can see they are both on the same ProjectId
• Lastly Seconds is the duration of the "original" scan
• and Seconds2 is the duration of the "overlapping" scan.

I want to calculate the saved time by having overlapping scans. Something like

(SUM Seconds2) - one row of the Seconds

2569 - 1513 = 1056 seconds (17.5 min) saved

If the scans ran serially the calculation would be

Seconds + Seconds2 + Seconds2
1513 + 1045 + 1524 = 4082 seconds (68 min)

Any assistance would be greatly appreciated

[OwenAuger · Accepted solution]

Hi @Anonymous 

Since you want to perform these calculations per ProjectId, you can use a SUMX( VALUES(...), CALCULATE(...) ) type pattern:

Saved Time =
SUMX (
    VALUES ( Projects[ProjectId] ),
    CALCULATE ( SUM ( Projects[Seconds2] ) - MAX ( Projects[Seconds] ) )
)

Scan Time Serial = 
SUMX ( 
    VALUES ( Projects[ProjectId] ),
    CALCULATE ( SUM ( Projects[Seconds2] ) + MAX ( Projects[Seconds] ) )
)

MAX is just an arbitrary aggregation for Projects[Seconds], since presumably this column contains a single distinct value for a given ProjectId.

Do these give the result you expect?

Regards,

Owen

[Anonymous]

Thank you Owen. Your suggestion worked as designed.