Rounding to nearest minute in DAX

[Radhika_Kanaka]

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

[mahoneypat]

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

NearestMinuteDAX = ROUND([Time]*1440,0)/1440

Pat

[Anonymous · Accepted 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.

[tackytechtom]

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/