Create stacked bar chart with different categories based on time intervals

[Anonymous]

Hello everyone,

I have a data set that shows me the duration of a call, time the call ended, date of the call, day of the week of the call as pictured below:

My goal is to create a stacked bar chart that splits each call into 2 categories: during work hours (7am-5pm Mon-Fri) and outside working hours (5pm-7am Mon-Fri, all day Sat-Sun). I was thinking of creating a new column in the Power Query editor with a True/False data type to see if each call is under working hours or not. I'm not sure however what the best way of doing it, or if I should do a measure instead.

Please let me know if you have any thoughts. Thank you!

[jdbuchanan71 · Accepted solution]

Hello @Anonymous ,

Try adding this as a calculated column in your table. 

Test =
IF (
    WEEKDAY ( Table1[Call Date] ) IN { 1, 7 } ||
        NOT (
            Table1[Call End Time (MST)] > TIME ( 8, 0, 0 )
                && Table1[Call End Time (MST)] < TIME ( 17, 0, 0 )
        ),
    "Out",
    "In"
)

[Anonymous]

Thanks guys, either solution works and does what I was looking for.

[XLBob · Accepted solution]

I'd like to add all my custom columns inside Power Query if possible.

so you can do this

if (Date.DayOfWeek([Call Date],Day.Monday)<5 and (Time.Hour([Call End Time])>=7 and Time.Hour([Call End Time])<=17))then 1 else 0

and this is what you will get

[jdbuchanan71 · Accepted solution]

Hello @Anonymous ,

Try adding this as a calculated column in your table. 

Test =
IF (
    WEEKDAY ( Table1[Call Date] ) IN { 1, 7 } ||
        NOT (
            Table1[Call End Time (MST)] > TIME ( 8, 0, 0 )
                && Table1[Call End Time (MST)] < TIME ( 17, 0, 0 )
        ),
    "Out",
    "In"
)