Forum Discussion
Duration Troubles
Vvelarde YES, this worked almost perfectly!! I had to make a couple tweeks, changing your semi-colons to commas and had to fix the minute & seconds sections to account for leading zeros when we have single digits (see bolded section). If you see anything off with this, let me know, but I can't thank you enough!!
DurationRUNNER =
VAR DurationRUNNER =
SUM ( RTB_Runs[Duration] )
RETURN
IF (
ROUNDDOWN ( DurationRUNNER, 0 ) > 1,
ROUNDDOWN ( DurationRUNNER, 0 ) * 24
+ HOUR ( DurationRUNNER )
& ":"
& (if(len(minute(DurationRUNNER)) > 1,minute(DurationRUNNER), "0" & minute(DurationRUNNER))
& ":"
& (if(len(second(DurationRUNNER)) > 1,second(DurationRUNNER), "0" & second(DurationRUNNER))
)),
FORMAT ( DurationRUNNER, "hh:mm:ss" )
)
Thank you Vvelarde!! this is almost there! Final question, the column totals are calculating a bit weird. Everything else looks great, but not sure how to address the column totals
I realize the grand totals for both months are off by 48 hours each, so it seems once it passes increments of 24 hours, it's dropping off. is there a way to just continue to total the hours indefinitly? This is the aggregation as it shows in excel. trying to replicate in Power BI.
| 5 | 6 | Grand Total | |
| Anna | 0:56:00 | 4:06:02 | 5:02:02 |
| Beth | 0:26:00 | 12:54:43 | 13:20:43 |
| Brian | 0:29:00 | 8:07:20 | 8:36:20 |
| Christian | 0:33:55 | 0:33:55 | |
| Erin | 0:45:00 | 5:15:00 | 6:00:00 |
| Heidi | 3:32:03 | 3:32:03 | |
| Jeremy | 0:26:00 | 3:29:29 | 3:55:29 |
| Jillian | 0:32:09 | 7:18:20 | 7:50:29 |
| Josh | 0:25:50 | 6:55:00 | 7:20:50 |
| Justin | 0:25:55 | 1:05:31 | 1:31:26 |
| Michelle | 3:16:30 | 3:16:30 | |
| Grand Total | 4:25:54 | 56:33:53 | 60:59:47 |
Hi heidibb
Try this measure (hope it works)
Duration-RUNNER =
VAR DurationRUNNER =
SUM ( Runners[Duration] )
RETURN
IF (
ROUNDDOWN ( DurationRUNNER; 0 ) > 1;
ROUNDDOWN ( DurationRUNNER; 0 ) * 24
+ HOUR ( DurationRUNNER )
& ":"
& (
MINUTE ( DurationRUNNER ) & ":"
& SECOND ( DurationRUNNER )
);
FORMAT ( DurationRUNNER; "hh:mm:ss" )
)Vvelarde YES, this worked almost perfectly!! I had to make a couple tweeks, changing your semi-colons to commas and had to fix the minute & seconds sections to account for leading zeros when we have single digits (see bolded section). If you see anything off with this, let me know, but I can't thank you enough!!
DurationRUNNER =
VAR DurationRUNNER =
SUM ( RTB_Runs[Duration] )
RETURN
IF (
ROUNDDOWN ( DurationRUNNER, 0 ) > 1,
ROUNDDOWN ( DurationRUNNER, 0 ) * 24
+ HOUR ( DurationRUNNER )
& ":"
& (if(len(minute(DurationRUNNER)) > 1,minute(DurationRUNNER), "0" & minute(DurationRUNNER))
& ":"
& (if(len(second(DurationRUNNER)) > 1,second(DurationRUNNER), "0" & second(DurationRUNNER))
)),
FORMAT ( DurationRUNNER, "hh:mm:ss" )
)
