Forum Discussion

Radhika_Kanaka's avatar
Radhika_Kanaka
Icon for Helper I rankHelper I
4 years ago
Solved

Rounding to nearest minute in DAX

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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