How to change decimal to time format (HH:MM:SS)

[Anonymous]

Hello guys, I encountered some problems while working with time-based data. 

I would like to round down (floor) the "time" column to 15mins and the returned results are in decimal format. I wonder how to change them to time format just like the "time column" 

Thanks for your help and time!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Use  below dax expression to create a calculated column:

floor = 
var a =FLOOR(MINUTE('Table'[Time]),15)
Return
TIME(HOUR('Table'[Time]),a,SECOND('Table'[Time]))

And you will see:

You can also create  a measure to achieve it.

Measure = 
var a=FLOOR(MINUTE(SELECTEDVALUE('Table'[Time])),15)
Return
TIME(HOUR(SELECTEDVALUE('Table'[Time])),a,SECOND(SELECTEDVALUE('Table'[Time])))

And you will see:

For the related .pbxi file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[TomMartens]

Hey @Anonymous ,

I use this approach to put time values into a bucket, the below DAX statement Is used to create a calculated column:

floored time = 
var h = HOUR('Table'[Time])
var m = FLOOR(MINUTE('Table'[Time]) , 15)
var s = 0
var flooredtime = TIME(h , m , s)
return flooredtime

This is the result:

Hopefully, this provides what you are looking for.

Regards,

Tom

Stay safe, stay healthy

[amitchandak]

Duration.minute can help to floor

https://docs.microsoft.com/en-us/powerquery-m/duration-minutes

Decimal to HH:MM:SS

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Refer: https://community.powerbi.com/t5/Desktop/Convert-Decimal-Duration-in-HH-MM-SS-format/m-p/959061

[Anonymous]

Please find the captured screen below.