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 po...
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

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,

         

         

        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.