Rounding time to nearest quarter

[midmurali]

Hey all,

I have a table with the following columns:

Time	Touch Type
13:00:58	1
13:06:09	3
13:06:35	4
13:06:45	2
13:09:12	1

And so on. I want to create a column, in which I have the rounded off time to the next quarter if the touch type is 1, and the rounded off time to previous quarter if the touch type is 4. So, ideally the output should be something like this:

Time	Touch Type	Rounded Off Time
13:00:58	1	13:15:00
13:06:09	3	13:06:09
13:06:35	4	13:06:35
13:06:45	2	13:00:00
13:09:12	1	13:15:00

Can someone help me with this? I tried using IF statement in a DAX calculated column but it showed up an error for some reason.

Thanks in advance!

M

[amitchandak · Accepted solution]

@midmurali , I only See changes for 2 and 4

a new column =

var _minute = Quotient(minute([Time]),15)

Switch( True() ,

[Touch Type] in {3,4} , [time] ,

[Touch Type]=2 , time(Hour([Time]), _minute *15,  0)

[Touch Type]=4 , if(( _minute +1) <4, time(Hour([Time]), (_minute+1) *15,  0)  , time(Hour([Time])+1, 0,  0) 

) )

[midmurali]

Hi Amit, sorry for the typo in my question. The changes are for Touch Types 1 and 2, not 1 and 4 as I originally mentioned.