Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

From Timestamp to mm:ss

Hi All, 

   I have a column in my dataset displaying the amount of time somebody stays on a call. 
Unfortunately the data type is: Time and it appears in the following way:

 
 

 

I would like to have it in the following format: mm:ss 
Additionally, I would like to sum them in order to have a aggregate number per person. 
Any ideas on how to do it?

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ,

     

    You need a calculated column to transform the date column to "mm:ss"：

    Column = FORMAT('Table'[Time],"nn:ss")

    then you will see as below:

    Then you need to create a measure to calculate the total minumtes for each person( if the time happens on the same day,otherwise you need to calculate the date differences between,then transformed to minutes or hours or seconds as you need):

    Measure 2 = 
var a=SUMX('Table',HOUR('Table'[Time]))
var b=SUMX('Table',MINUTE('Table'[Time]))
var c=SUMX('Table',SECOND('Table'[Time]))
return 
a*60+b+c/60

    And you will see:

     

     

    For the related .pbix file,pls click here.

     

     

    Best Regards,

    Kelly

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You need a calculated column to transform the date column to "mm:ss"：

    Column = FORMAT('Table'[Time],"nn:ss")

    then you will see as below:

    Then you need to create a measure to calculate the total minumtes for each person( if the time happens on the same day,otherwise you need to calculate the date differences between,then transformed to minutes or hours or seconds as you need):

    Measure 2 = 
var a=SUMX('Table',HOUR('Table'[Time]))
var b=SUMX('Table',MINUTE('Table'[Time]))
var c=SUMX('Table',SECOND('Table'[Time]))
return 
a*60+b+c/60

    And you will see:

     

     

    For the related .pbix file,pls click here.

     

     

    Best Regards,

    Kelly