Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to round the time to the nearest minute. Can someone help me to achieve this in DAX?
Below is the sample data.
0:11:59 -> 11 Minutes 59 Seconds (12)
0:19:29 -> 19 Minutes 29 Seconds (19)
0:00:00 -> (Not Included)
0:00:00 -> (Not Included)
0:48:51 -> 48 Minutes 51 Seconds (49)
9:06:55 -> 546 Minutes 55 Seconds (547)
0:11:05 -> 11 Minutes 5 Seconds (11)
1:01:45 -> 61 Minutes 45 Seconds (61)
Thanks ,
Radhika
Solved! Go to Solution.
Hi @Radhika_Kanaka ,
Please try:
Column =
VAR _minutes =
HOUR ( [Time] ) * 60
+ MINUTE ( [Time] )
VAR _round =
IF ( SECOND ( [Time] ) < 30, _minutes, _minutes + 1 )
RETURN
IF (
[Time] = TIME ( 0, 0, 0 ),
"Not Included",
_minutes & " Minutes "
& SECOND ( [Time] ) & " Seconds (" & _round & ")"
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can use this expression to round to the nearest minute. Convert the resulting column to type time.
In Query Editor
= Number.Round(Number.From([TimeColumn]) * 1440, 0)/1440
With DAX
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Radhika_Kanaka ,
Please try:
Column =
VAR _minutes =
HOUR ( [Time] ) * 60
+ MINUTE ( [Time] )
VAR _round =
IF ( SECOND ( [Time] ) < 30, _minutes, _minutes + 1 )
RETURN
IF (
[Time] = TIME ( 0, 0, 0 ),
"Not Included",
_minutes & " Minutes "
& SECOND ( [Time] ) & " Seconds (" & _round & ")"
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Radhika_Kanaka ,
How about this:
Here the DAX:
TimeRound = MROUND ( Table[Time], TIME ( 0, 1, 0 ) ) + TIME ( 0, 0, 0 )
Let me know if this helps 🙂
[EDIT]
I read your requirement again and here the column I think you are actually after 🙂
Here the DAX:
MinutesRoundTotal = 60 *HOUR(TableTime[Time]) + MINUTE ( MROUND ( TableTime[Time], TIME ( 0, 1, 0 ) ) + TIME ( 0, 0, 0 ) )
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |