PowerBi Duration calculation in hh:mm:ss

Question

I have the following DAX in PowerBi to calculate duration, anyone had an idea how to include the seconds?

Total Duration = sumx('Sheet1'; hour('Sheet1'[Duration])) +
TRUNC(sumx('Sheet1'; MINUTE('Sheet1'[Duration]))/60) & ":" & 
mod(sumx('Sheet1'; MINUTE('Sheet1'[Duration])); 60)& ":00"

Alexis Olson · Accepted Answer · 2021-09-14T15:03:24.557

6

Try this instead:

= FORMAT(SUM('Sheet1'[Duration]), "hh:mm:ss")

Edit: If the duration can cover more than 24 hours, then you may need to handle days separately. Something like this:

Format Duration (d.hh:mm:ss) =
VAR TotalDuration = SUM ( 'Sheet1'[Duration] )
VAR TotalDays = TRUNC ( TotalDuration )
VAR HrMinSec = FORMAT ( TotalDuration - TotalDays, "hh:mm:ss" )
RETURN
    TotalDays & "." & HrMinSec

To keep everything in hours:

Format Duration = 
VAR TotalDuration = SUM ( 'Sheet1'[Duration] )
VAR TotalHours = TRUNC ( 24 * TotalDuration )
VAR MinSec = FORMAT ( TotalDuration - TotalHours / 24, "nn:ss" )
RETURN
    TotalHours & ":" & MinSec

edited Sep 14 '21 at 15:03

answered May 17 '18 at 15:12

Alexis Olson

36,898
7
37
62

This will fail if duration (and OP specifically said *duration*) exceeds 24 hours. – MrUpsidown Sep 14 '21 at 14:34
One could add `if (TotalHours < 10, "0" & TotalHours, TotalHours) & ":" ...` in case you need the leading `0`, ie. `01:30`. – MrUpsidown Sep 14 '21 at 15:17
1

I'd recommend `FORMAT ( TotalHours, "00" )` instead of the `IF` you suggested for left padding. – Alexis Olson Sep 14 '21 at 15:21

score 0 · Answer 2 · answered May 25 '18 at 10:46

0

If the data is in seconds you can use:

=FORMAT((DATAINSECONDS/3600)/24; "HH:mm:ss")

answered May 25 '18 at 10:46

Lucia Clifford

86
6

score 0 · Answer 3 · edited Mar 21 '19 at 10:44

0

=FORMAT((DATAINSECONDS/3600)/24; "HH:mm:ss")

This solution works with time but doesn't work with duration. If your duration less than 24 hours, time and duration are the same, and the solution works. But if your duration exceeds 24 hours this solution doesn't work. For example, try it for 87000 seconds. Better solution in Power BI is to trasform to duration in power query.

edited Mar 21 '19 at 10:44

EvilDr

8,131
11
66
121

answered Jul 31 '18 at 14:36

Anton Gerasimov

1

score 0 · Answer 4 · edited Aug 14 '18 at 13:18

0

Total_ = sumx('dados'; hour('dados'[Duration])) + TRUNC(sumx('dados';
         MINUTE('dados'[Duration]))/60) & ":" & mod(sumx('dados'; 
         MINUTE('dados'[Duration])); 60) + 
         TRUNC(SUMX('dados';SECOND('dados'[Duração]))/60) & ":" & 
         mod(sumx('dados'; SECOND('dados'[Duration])); 60)

edited Aug 14 '18 at 13:18

Vadim Kotov

7,766
8
46
61

answered Aug 13 '18 at 20:59

Juliano

1
1

try to enclose your code part of the solution within backtick quotes. See the formatting help page of stackoverflow : https://stackoverflow.com/editing-help – ggulgulia Aug 13 '18 at 21:34
1

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited, immediate help. A [proper explanation would greatly improve its long-term value](//meta.stackexchange.com/q/114762/206345) by showing _why_ this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Blue Aug 13 '18 at 23:32

PowerBi Duration calculation in hh:mm:ss

4 Answers4

Linked