Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
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
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
Please find the captured screen below.
User | Count |
---|---|
106 | |
90 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |