Grouping times

[SHAKEDALROY]

Hi,

I have a Table with data about Traffic Reports

ViolationCode, IssuerKey, DateOfViolation,TimeOfViolation etc..

I need to create a vizualization that shows the amount of violations for every two hours.

Therfore,

I need to group a set of times throughout a day into different categrories ( Every two hours)

i.e. 8am-10pm is group 8 to 10 am. 10am to 12pm is group 10 to 12 am and so on.

The grouping/bin fucntion  is still not working for me.

can anyone please provide some help for a formula?

[AlB · Accepted solution]

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 

[v-alq-msft · Accepted solution]

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.

[CNENFRNL]

Hi, @SHAKEDALROY , you might want to use these simple codes to create a bin table named Bins,

let
    Source = Table.AddColumn(Table.TransformColumns(Table.FromRows(List.Split({0..23},2), {"start","end"}), {{"end", each _+1}}), "bin", each Text.From([start]) & "h to " & Text.From([end]) & "h")
in
    Source

Then a DAX formula to count records in each period,

Count =
COUNTROWS (
    FILTER (
        Report,
        HOUR ( Report[TimeOfViolation] ) >= MIN ( Bins[start] )
            && HOUR ( Report[TimeOfViolation] ) < MIN ( Bins[end] )
    )
)

I might want to refer to the attached file for details.

[AlB · Accepted solution]

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