Forum Discussion
Create stacked bar chart with different categories based on time intervals
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!
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" )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
3 Replies
- jdbuchanan71
Super User
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" ) 
XLBobResolver II
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