Help Please with an IF Statement with Time Values to create Interval Groups

[blfox33]

Hi all,

My apologies in advance as I haven't ever asked a question using this community before but wanted to say "thank you" to the community because this support group has helped me out several times before 🙂 

I have been working on a way to create a new column in Power BI for a little over a week now and I can't seem to grasp what I need to do. This is a side project that I am trying to help a coworker with so I haven't been able to put too much time into it but have reached the point where I am out of ideas.

My goal is to take a column that has previously been converted from a 'Date/Time" field into just a Time column. Now, I need to take that time column and create a new column that looks at the time and then based on the conditions below, returns the time slots back to me. It is easier to show below:

Column Name with just Time in it:  Helper 

Time Slot Conditions:

If [Helper] >= 07:30 to 10:30, "Breakfast"

If [Helper] >= 12:00 to 15:30, "Lunch"

If [Helper] >= 16:30 to 18:30, "Snacks"

If [Helper] >= 19:30 to 23:00, "Dinner"

If [Helper] >= 12:00 to 04:00, "Midnight"

Else "Other Snacks"

I will admit that I have tried all kinds of formulas but none of them seem to work and give error messages. Can anyone please help because I am at a loss.

Thank you!

[Greg_Deckler]

@blfox33 Time values are fractions of a day, like 1/24/60 would be in minutes so maybe something like:

Time Slot Conditions Column:
VAR __Minutes = 1/24/60
VAR __MinutesInHour = 60 * __Minutes
VAR __Helper = [Helper]
VAR __Resut = 
  SWITCH( TRUE(),
    [Helper] >= __MinutesInHour * 7.5 && <= __MinutesInHour * 10.5, "Breakfast",
    [Helper] >= __MinutesInHour * 16.5 && <= __MinutesInHour * 18.5, "Snacks",
    [Helper] >= __MinutesInHour * 19.5 && <= __MinutesInHour * 23, "Dinner",
    [Helper] >= __MinutesInHour * 0 && <= __MinutesInHour * 4, "Midnight",
    "Other Snacks"
  )
RETURN
  __Result

[jgeddes · Accepted solution]

This seems like a good case to use a SWITCH statement in a calculated column.

Something like this should get you pointed in the right direction. (I used [Time] as the column with times in it where you had [Helper].)

_condition =

SWITCH(

    TRUE(),

    AND([Time] >= TIME(7,30,0), [Time] <= TIME(10,30,0)), "Breakfast",

    AND([Time] >= TIME(12,0,0), [Time] <= TIME(15,30,0)), "Lunch",

    AND([Time] >= TIME(16,30,0), [Time] <= TIME(18,30,0)), "Snacks",

    AND([Time] >= TIME(19,30,0), [Time] <= TIME(23,0,0)), "Dinner",

    AND([Time] >= TIME(0,0,0), [Time] <= TIME(4,0,0)), "Midnight",

    "Other Snacks"

)

[blfox33]

Hello! Oh my goodness, it is working now!!! Thank you so much!