Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

7 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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's avatar
    Anonymous
    Not applicable
    7 years ago

    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