How to create custom buckets of time

[ScORE]

Hello, 

 I am wondering if anyone can tell me how to create custom groupings (buckets) of time for my data.  Every row item has a different time that can occur any hour or minute between 12:00:00 a.m. - 11:59:00 p.m.  I would like to group certain timeframes togetherto reflect the following timeframes: 

6:00 a.m. - 8:30 a.m.
8:30 a.m. - 9:30 a.m

9:30 a.m. to noon
noon to 3:30 p.m.
3:30 p.m. to 6:00 p.m.
6:00 p.m. - 6:00 a.m.

Is this possible?  ANy help is greatly appreciated!

Thanks,

S

[v-ljerr-msft · Accepted solution]

Hi @ScORE,

I assume you have a table called "Table1" with a DateTime type column called "DateTime" like below.

Then you should be able to use the formula below to create a new calculate column to group timeframes.

Column = 
SWITCH (
    TRUE (),
    HOUR ( Table1[DateTime] )
        + MINUTE ( Table1[DateTime] ) / 60
        >= 6
        && HOUR ( Table1[DateTime] )
            + MINUTE ( Table1[DateTime] ) / 60
            < 8.5, "6:00 a.m. - 8:30 a.m.",
    HOUR ( Table1[DateTime] )
        + MINUTE ( Table1[DateTime] ) / 60
        >= 8.5
        && HOUR ( Table1[DateTime] )
            + MINUTE ( Table1[DateTime] ) / 60
            < 9.5, "8:30 a.m. - 9:30 a.m",
    HOUR ( Table1[DateTime] )
        + MINUTE ( Table1[DateTime] ) / 60
        >= 9.5
        && HOUR ( Table1[DateTime] )
            + MINUTE ( Table1[DateTime] ) / 60
            < 12, "9:30 a.m. to noon",
    HOUR ( Table1[DateTime] )
        + MINUTE ( Table1[DateTime] ) / 60
        >= 12
        && HOUR ( Table1[DateTime] )
            + MINUTE ( Table1[DateTime] ) / 60
            < 15.5, "noon to 3:30 p.m.",
    HOUR ( Table1[DateTime] )
        + MINUTE ( Table1[DateTime] ) / 60
        >= 15.5
        && HOUR ( Table1[DateTime] )
            + MINUTE ( Table1[DateTime] ) / 60
            < 18, "3:30 p.m. to 6:00 p.m.",
    "6:00 p.m. - 6:00 a.m."
)

Regards

[Capt_Blueballs]

Hi v-ljerr-msft & ScORE,

Excellent Q&A! I want to do the exact same thing. I want to analyse what the most sold items are during particular parts of the day. Therefore I would like to use the following bins:

before noon - 12:30 (opening)

12:30 - 14:00 (peak hours 1)

14:00 - 17:30 (afternoon)

17:30 - 19:00 (peak hours 2)

19:00 - after 19:00 (closing)

Unfortunately your formula does not work for me. Perhaps because -as you noticed- I use European time (24:00). I can change my source data to US (am/pm), but Power BI changes them right back on importing without given me the option to use US (am/pm).

I did change the 'table1' to 'Sales' as that it the name of the table. And 'Datetime' to 'Timestamp', the column. As for the formula, that is a little bit of unknown territory.

Could you help me please?

Thank you in advance & Kind regards,

George

[Capt_Blueballs]

Hi v-ljerr-msft & ScORE,

This is the same challenge as I'm dealing with! I want to analyse what general sales are done during different parts of the day.

Therefore I want to make time buckets, for instance:

prior-12:00 (startup)

12:00-14:00 (peak hour 1)

14:00-17:30 (afternoon)

17:30-19:00 (peak hour 2)

19:00 - later (closeing)

Unfortunately your proposed formula gives me an error. I already changed table1 to 'Sales' as that it the name of the table. And Datetime to Timestamp. Perhaps it has something to do that your formula is based upon the US notation of time (12:00 AM/PM) and mine is European (24:00). However I can change the source (Excel) easily to match, but then Power BI changes it right back to European. Without the option to change it to the American notation.

Could you possibly rewrite the formula, or help me on my way?

Thank you very much & Regards,

Sjors

[ScORE]

This worked great!  Thanks for your assistance.  I need to master the SWITCH function.