topic Re: How to Calculate time saved with overlapping time in DAX Commands and Tips

How to Calculate time saved with overlapping time

Anonymous — Fri, 23 Apr 2021 16:19:10 GMT

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.

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 — Sat, 24 Apr 2021 09:07:04 GMT

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 — Thu, 29 Apr 2021 11:11:25 GMT

Thank you Owen. Your suggestion worked as designed.