Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to convert a Text data type to Time Data type ( HH:MM) ?

Hi Team,   Quick help needed please   I have created a calculated column based on a column which is in time duration ( Seconds) .    Column Name : Time Duration ( Seconds)    Sample Data :   ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

     

    You may try below measure:

    Measure =
FORMAT ( AVERAGE ( 'Major Incident'[Time] ), "HH:MM" )

    If it is not your case,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

     

    Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous 

 

You may create the column as below and then change the format.

Time = 
VAR a = 'Major Incident'[ Time Durations(Seconds) ]
VAR hours =
    INT ( a / 3600 )
VAR minutes =
    INT ( MOD ( a - ( hours * 3600 ), 3600 ) / 60 )
VAR seconds =
    ROUNDUP ( MOD ( MOD ( a - ( hours * 3600 ), 3600 ), 60 ), 0 )
RETURN
    TIME ( hours, minutes, seconds )

Regards,

Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Cherie,

     

    Thanks for your reply. I have created a column and updated my report . But, I still see some issue . Please check the attached screenshot. 

     

    Time to Resolve-Major is a column which has data in Seconds. So if we take first row, it has 626220 seconds .

     

    I manually divided it which should be 626220/3600 = 173.95 hours . But I am getting 05:57 (hours mins) using your formulae .

     

    Note: Time is the column which holds the data created using your formuale.   Am I doing some thing wrong ? Could you please assist ?

     

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

      Hi Anonymous 

       

      If you want to change the value to Time format. The value should be in 24 hours.If the hours are >24,it could not be changed to time format.So my formula is calculated in 24 hours.

      Regards,

      Cherie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Cherie,

         

        Thanks for your reply. I have understood the 24 hour format . I have one more thing in addition to this. 

         

        Now that we have got time duration in HH:MM format for all records in table . I want to take an average out of it and show that in a Card thats available in Power BI Visuals. 

         

        It is either showing First or Last Duration , but not average. Can you help me with this ?