# Power BI Exchange

View Only

## Sum a Duration

• #### 1.  Sum a Duration

Bronze Contributor
Posted Sep 05, 2019 04:20 AM
Hi

I have a table with working hours and minutes (come and go). I want to summarize it. Format HH:MM does not work. Any ideas?

DurationDayDec is the duration in days as decimal -> 1.5 days

Duration =
FORMAT(
SUM(Zeiterfassung[DurationDaysDec]),
"HH:mm"
)
--> 11:55 is not korrekt -> should be 35:55

Best Andreas

------------------------------
Andreas Ratz
CEO
Widnau
+41798055000
------------------------------

• #### 2.  RE: Sum a Duration

Top Contributor
Posted Sep 05, 2019 05:56 AM

A little help thru this url and little bit of fine tune. I think it should work for you.

```Duration =
VAR TotalSeconds=SUMX(Zeiterfassung,HOUR(Zeiterfassung[DurationDaySec])*3600+MINUTE(Zeiterfassung[DurationDaySec])*60+SECOND(Zeiterfassung[DurationDaySec]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)​```

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 3.  RE: Sum a Duration

Bronze Contributor
Posted Sep 05, 2019 06:26 AM

Hi Ahmed

Works!!! Thx a lot

Best

Andreas

Mit freundlichen Grüssen

Andreas Ratz

Unterlettenstrasse 10

CH-9443 Widnau

Tel. +41  79 805 5000

• #### 4.  RE: Sum a Duration

Bronze Contributor
Posted Sep 07, 2019 07:19 AM
May I have your help again?

In the mean time I have a table with the plannend hours per month

 YearMonth Arbeitstage Urlaubstage Hours Plan Dec Days Plan Dec 201807 22 2.08333 39.83334 1.6597225 201808 22 2.08333 39.83334 1.6597225 201809 21 2.08333 37.83334 1.576389167

I have copied the Code and modified a little bit to calculate the measure for the planned hours

Hours Plan =
VAR TotalSeconds=
SUMX(Sollarbeitszeit,
HOUR(Sollarbeitszeit[Days Plan Dec])*3600+
MINUTE(Sollarbeitszeit[Days Plan Dec])*60+
SECOND(Sollarbeitszeit[Days Plan Dec]))
VAR Days =
TRUNC(TotalSeconds/3600/24)
VAR Hours =
TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =
TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs =
MOD(TotalSeconds,60)
return
IF((Hours + (Days*24))<10,"0"&(Hours + (Days*24)),(Hours + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

The result in the report is

Any Idea what I have made wrong?

Best
Andreas

------------------------------
Andreas Ratz
CEO
Widnau
+41798055000
------------------------------

• #### 5.  RE: Sum a Duration

Top Contributor
Posted Sep 10, 2019 11:54 PM
HI @Andreas Ratz​,

It seems that hour function not capturing days information. So adding # of days in formula will result correct.

```Hours Plan =
VAR TotalSeconds=
SUMX(Sollarbeitszeit, 24*MOD(Sollarbeitszeit[Days Plan Dec],1) *3600 + // Adding Day since hour function not returning days number
HOUR(Sollarbeitszeit[Days Plan Dec])*3600+
MINUTE(Sollarbeitszeit[Days Plan Dec])*60+
SECOND(Sollarbeitszeit[Days Plan Dec]))
VAR Days =
TRUNC(TotalSeconds/3600/24)
VAR Hours =
TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =
TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs =
ROUND(MOD(TotalSeconds,60),2)
return
IF((Hours + (Days*24))<10,"0"&(Hours + (Days*24)),(Hours + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)​```

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 6.  RE: Sum a Duration

Bronze Contributor
Posted Sep 11, 2019 03:56 AM
Edited by Andreas Ratz Sep 11, 2019 04:33 AM
Hi @Farhan Ahmed

Thx again for your help. We are very close now. But still some hours are missing. The result now is ​

The source shows

Replaced your function to capture the days by

24 * ROUNDDOWN(Sollarbeitszeit[Days Plan Dec],0) * 3600

Is this correct?

Best
Andreas

------------------------------
Andreas Ratz
CEO
Widnau
+41798055000
------------------------------

• #### 7.  RE: Sum a Duration

Top Contributor
Posted Sep 11, 2019 05:17 AM
If ROUNDDOWN not giving you correct results then Try use FLOOR instead of ROUNDDOWN

24*FLOOR(Sollarbeitszeit[Days Plan Dec],1)

------------------------------
Farhan Ahmed
karachi
3452523688
------------------------------

• #### 8.  RE: Sum a Duration

Bronze Contributor
Posted Sep 11, 2019 11:08 AM
ROUNDDOWN works fine

------------------------------
Andreas
------------------------------