How to get duplicates from a 4 -hour interval

[zettipasta]

Hello everyone, I'm new to PowerBi and it's my first time asking in the forum.

May I ask how to get duplicates from a 4-hour interval (given that I created a Calendar and Time Table using PowerQuery).

IF ID from the latest date & time has a duplicate from the previous 4 hours,  then it should get the output as "HOLD";

IF ID from the previous 4 hours thas no duplicate from the latest date & time, it should be "RELEASED";

IF a new ID (with no duplicates) entered in the latest date & time, it would get the output as "HOLD"

Here is a sample table for your reference. 

Thank you! I appreciate the help I can get.

[amitchandak · Accepted solution]

@zettipasta ,

New column =

var _time = [DateTime] -time(4,0,0)

var _cnt =countx(filter(Table, [ID] = earlier([ID]) && [DateTime]>= _time && [DateTime]< earlier([Datetime])  ), [ID])

var _cntall =countx(filter(Table, [ID] = earlier([ID]) && [DateTime]< earlier([Datetime]) ), [ID])

return

Switch(True(),

not(isblank(_cnt)), "Hold",

isblank(_cnt) && not(isblank(_cntall)), "Release",

"Hold"

)

[zettipasta]

Hello! This was extremely helpful! Thank you soo much