Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Employee
Employee

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

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.
Anonymous
Not applicable

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.

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

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.
Anonymous
Not applicable

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. 

Hi @Anonymous

 

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.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.