Grouping times

Hi SHAKEDALROY 

Assuming you have a Table1[DateTime] column in your fact table, create another column that specifies the corresponding two-hour category.

Two-hour category =
VAR time_ =
    Table1[DateTime] - INT ( Table1[DateTime] )
RETURN
    SWITCH (
        TRUE (),
        time_ < 2 / 24, "00:00-02:00",
        time_ < 4 / 24, "02:00-04:00",
        time_ < 6 / 24, "04:00-06:00",
      // Fill in all the remaining options here....
      //...  
        time_ < 24 / 24, "22:00-00:00"
    )

or alternatively:

Two-hour category V2 =
VAR time_ = Table2[DateTime] - INT ( Table2[DateTime] )
VAR aux_ = FLOOR ( time_ * 24, 2 )
RETURN
    FORMAT ( aux_, "00" ) & "-" & FORMAT ( aux_ + 2, "00" )

You can then use this column in a chart for instance and easily build a measure that calculates the number of items per two-hour category

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Hi, SHAKEDALROY 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Test:

You may create a custom column with the following m codes.

let 
d = [Date],
h = Time.Hour(d)
in 
if h>=0 and h<2 then "0-2"
else if h<4 then "2-4"
else if h<6 then "4-6"
else if h<8 then "6-8"
else if h<10 then "8-10"
else if h<12 then "10-12"
else if h<14 then "12-14"
else if h<16 then "14-16"
else if h<18 then "16-18"
else if h<20 then "18-20"
else if h<22 then "20-22"
else if h<24 then "22-24"
else null

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.