Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
Solved! Go to 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
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
Thank you Owen. Your suggestion worked as designed.