cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DamodarReddy Frequent Visitor
Frequent Visitor

add time

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: add time

Hi @DamodarReddy

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: add time

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Community Support Team
Community Support Team

Re: add time

Hi @DamodarReddy

 

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

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DamodarReddy Frequent Visitor
Frequent Visitor

Re: add time

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.

Community Support Team
Community Support Team

Re: add time

Hi @DamodarReddy

 

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DamodarReddy Frequent Visitor
Frequent Visitor

Re: add time

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. 

Community Support Team
Community Support Team

Re: add time

Hi @DamodarReddy

 

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

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.