Bucketing with respect to time for each ID based on date and time

[jeffrock]

Hi 

I need to create a bucket system for each 1.2 Hours.

I have Multiple rows with different times with same unique ID.

I already created time slots

but lets consider a use case

I have a set of ID's below, I need the following result under Bucket column

ID	Time	Bucket
231	10/08/2023 20:00:00	7
231	10/08/2023 22:00:00	8
231	10/08/2023 23:59:00	9
231	11/08/2023 02:00:00	10
233	12/08/2023 22:00:00	8
233	12/08/2023 23:59:00	9
233	13/08/2023 02:00:00	10

So while bucketing it should consider the Date as well so that the bucketing is in a sequence

I am using below Code to bucket

BucketColumn =
SWITCH(
TRUE(),
Task[Completed_Time] <= TIME(1, 12, 0), 1,
Task[Completed_Time] <= TIME(2, 24, 0), 2,
Task[Completed_Time] <= TIME(3, 36, 0), 3,
Task[Completed_Time] <= TIME(4, 48, 0), 4,
Task[Completed_Time] <= TIME(6, 0, 0), 5,
Task[Completed_Time] <= TIME(7, 12, 0), 6,
Task[Completed_Time] <= TIME(8, 24, 0), 7,
Task[Completed_Time] <= TIME(9, 36, 0), 8,
Task[Completed_Time] <= TIME(10, 48, 0), 9,
Task[Completed_Time] <= TIME(12, 00, 0), 10,
Task[Completed_Time] <= TIME(13, 12, 0), 1,
Task[Completed_Time] <= TIME(14, 24, 0), 2,
Task[Completed_Time] <= TIME(15, 36, 0), 3,
Task[Completed_Time] <= TIME(16, 48, 0), 4,
Task[Completed_Time] <= TIME(18, 0, 0), 5,
Task[Completed_Time] <= TIME(19, 12, 0), 6,
Task[Completed_Time] <= TIME(20, 24, 0), 7,
Task[Completed_Time] <= TIME(21, 36, 0), 8,
Task[Completed_Time] <= TIME(22, 48, 0), 9,
Task[Completed_Time] <= TIME(23, 59, 59), 10,
TRUE(), 11
)

But the thing is the entries made after 00:00:00 is bucketed under 1, rather it should be bucketed last as it is finished the next day

Appreciate your help in solving this

Thank you

[v-shex-msft · Accepted solution]

HI @jeffrock,

You can try to use following calculated column formula to setting segments based on specific interval:

Bucket Group =
VAR currTime =
    TIMEVALUE ( 'Table'[Time] )
VAR totalSecond =
    HOUR ( currTime ) * 3600
        + MINUTE ( currTime ) * 60
        + SECOND ( currTime )
VAR interval = 72 * 60
VAR segmentation =
    INT ( totalSecond / interval )
VAR offset =
    IF ( MOD ( totalSecond - segmentation * interval, interval ) > 0, 1, 0 )
RETURN
    IFERROR ( MOD ( segmentation, 10 ) + offset, 11 )

Regards,

Xiaoxin Sheng