Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |