Hi everyone!
I'm trying to visualize the bookings of different rooms and I have sample data below. I have Start Dates with time and End Dates with time and have created the column Booked Dates that goes from start to end with an increment of 1 hour.
Any ideas how I can visualize the booked time vs. the unbooked time? General tips would be greatly appreciated as I'm stuck. I would like to be able to show the intensity of the bookings, like for a gym, both with weekday and time of day. I've tried many approaches, for example counting the number of DistinctFridays, which consistently return one day less than what is booked. Index for Booking = 0 is booked two Fridays, but it returns 1.
DistinctFridays =
SUMX(
VALUES('TEST'[Index for Booking]),
VAR CurrentBookingIndex = 'TEST'[Index for Booking]
VAR DistinctFridayDates =
SUMX(
VALUES('TEST'[Dates Booked]),
VAR TESTAR =
CALCULATE(
COUNTROWS('TEST'),
FILTER(
ALL('TEST'),
'TEST'[Weekday Name] = "Friday" &&
'TEST'[Index for Booking] = CurrentBookingIndex &&
'TEST'[Dates Booked] = EARLIER('TEST'[Dates Booked])
)
)
RETURN
IF(TESTAR > 0, 1, 0)
)
RETURN
DistinctFridayDates
)
Index for OrderIndex for BookingLab Area RoomLab AreaRoomStart DateEnd DateDates BookedWeekday NameSingle DayDuration DaysDuration HoursDuration Days GroupingDuration Days Grouping Index
| 0 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 09:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 1 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 10:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 2 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 11:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 3 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 12:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 4 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 13:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 5 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 14:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 6 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 15:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 7 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 16:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 8 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 17:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 9 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 18:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 10 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 19:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 11 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 20:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 12 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 21:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 13 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 22:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 14 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-18 23:00:00 | Friday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 15 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-19 00:00:00 | Saturday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 16 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-19 01:00:00 | Saturday | FALSE | 7 | 179 | 3 - 7 days | 3 |
| 17 | 0 | PV23.3,P02 | PV23.3 | P02 | 2023-08-18 09:00:00 | 2023-08-25 20:00:00 | 2023-08-19 02:00:00 | Saturday | FALSE | 7 | 179 | 3 - 7 days | 3 |
