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 S...
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.

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/