Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.