Duration calculation in hh:mm:ss using Power BI

Question

I have the following DAX in Power BI to calculate duration. How can I 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"

score 6 · Accepted Answer · edited Aug 19 '22 at 01:44

6

Try this instead:

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

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 Aug 19 '22 at 01:44

Peter Mortensen

30,738
21
105
131

answered May 17 '18 at 15:12

Alexis Olson

38,724
7
42
64

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

136
1
6

score 0 · Answer 3 · edited Aug 19 '22 at 02:19

0

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

This solution works with time, but it 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.

A better solution in Power BI is to transform to duration in a power query.

edited Aug 19 '22 at 02:19

Peter Mortensen

30,738
21
105
131

answered Jul 31 '18 at 14:36

Anton Gerasimov

1

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

-1

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

8,084
8
48
62

answered Aug 13 '18 at 20:59

Juliano

1
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
2

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

Duration calculation in hh:mm:ss using Power BI

4 Answers4

Linked