add time

[Anonymous]

I have to sum the time in a particular format.

for Example :

9:26:22

9:22:14

9:12:27

9:05:25

9:15:24

Sum is 46:21:52 - i have to get in this format.

But, when i am trying to do in power i am getting the sum as 22:21:52.

i am trying to change the format in power bi, i am not getting the sum(46:21:52).

In Excel we are changing the format to get sum like(46:21:52) which is  [h]:mm:ss:@

These types of formats are not available in Power bi.

is there any alternate solution for this.

[Anonymous]

Hi,

Recently i am facing one issue, I am not able share the dashboards to other people  in my team and everyone are having the pro license, but still not able share the dashboard through the OUTLOOK.

Please reply me ASAP.

thanks and Regards,

Damodar Reddy

[v-cherch-msft]

Hi @Anonymous

You may create below measure.

NewTime =
VAR a =
    SUMX (
        'Time',
        HOUR ( 'Time'[Time] ) * 3600
            + MINUTE ( 'Time'[Time] ) * 60
            + SECOND ( 'Time'[Time] )
    )
VAR hours =
    TRUNC ( a / 3600 )
VAR minutes =
    TRUNC ( MOD ( a, 3600 ) / 60 )
VAR seconds =
    MOD ( a, 60 )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds

Regards,

Cherie

[Anonymous]

New Time = var a = SUMX(Hour(Table1[Time])*3600 + MINUTE(Table1[Time])*60 +SECOND(Table1[Time]) var hours = TRUNC(a/3600) var minutes = TRUNC(MOD(a,3600)/60) var seconds = MOD(a,60) return hours & ":" & minutes & ":" & seconds

i am using two columns one is date & time

     Date                    Time

 1/7/2017               9:16:25

 2/7/2017               9:21:30

 3/7/2017               9:22:45

when i an using as columns i am not able to get the name of the column SumX(Hour(i am not getting here that column(Time)).

if i create a measure like Sum(table1(time)). then i can use that measure over here  SUMX(Hour(Table1[Time])*3600. Even tough i am using this i am getting error i.e, your are using to many hour arguments.

Please help on this.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may add Table1 in your formula. Attached the sample file.

New Time =
VAR a =
    SUMX (
        Table1,
        HOUR ( Table1[Time] ) * 3600
            + MINUTE ( Table1[Time] ) * 60
            + SECOND ( Table1[Time] )
    )
VAR hours =
    TRUNC ( a / 3600 )
VAR minutes =
    TRUNC ( MOD ( a, 3600 ) / 60 )
VAR seconds =
    MOD ( a, 60 )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds

Regards,

Cherie

[Anonymous]

Hi,

Example :   In Excel - 4:46:35 we can change that format to 64:46:35(Custom format - [h]:mm:ss;@)

But in power BI  how can we change that this time (4:46:35) to 64:46:35.

Please reply ASAP. 

[v-cherch-msft]

Hi @Anonymous

There's no custom format in power bi.You may create a calculated column to get it.

Regards,

Cherie

[Greg_Deckler]

I have done a bunch of different stuff regarding time/duration type of stuff. Check out these links:

https://community.powerbi.com/t5/Quick-Measures-Gallery/TIMEADD/m-p/585280

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279