Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
77 | |
48 | |
46 | |
20 | |
16 |