Power BI Hotel Booking Calendar Visual Available and booked days, Maintenance and Out of Order. Power App Embedded

 

I slightly adjusted the data model and Power App by removing Room Status in Rooms list and adding Choice column in Booking List. Now when booking we can block the days for Maintenance and Out of Order.

I sketched a quick Power BI Report , connected to my SharePoint List with data model and also embedded Power App. Now the data can be added directly to PBI and updated after refreshing. Unfortunately, I have Free PBI on my home machine so I could not publish and set Power Automate to refresh my dashboard. 

Again, budget variant! :)

My Data Model is based on  3 SharePoint Lists and 3 Tables, generated / created in Power BI.

Simple DateKey Table 

DateKey = CALENDAR(Min(Bookings[DateFrom]),max(Bookings[DateTo])+30)

And few columns: 

MonthNum = Month([Date])

Month = Format([Date],"mmm")

Year = Year([Date])

Month Year = [Month] &"-"& [Year]

biRoomCalendar was created by using Generate Formula.

biRoomCalendar = Generate(Calendar(Min(Bookings[DateFrom]),Max(Bookings[DateTo])),Rooms)

Added few columns:

RoomId-Day = [Id]&[Date]

Current Status = If(Not(IsBlank(LOOKUPVALUE(biBookingCalendar[BookingType],

biBookingCalendar[RoomId-Day],[RoomId-Day]))),

Left(LOOKUPVALUE(biBookingCalendar[BookingType],

biBookingCalendar[RoomId-Day],[RoomId-Day]),1),If([Date]<Today(),"V","A"))

DateShrt = Format([Date],"d.m")

Also used Generate for biBookingCalendar

biBookingCalendar = var C = CALENDARAUTO ()

Return GENERATE(Bookings,filter(c,[Date]>= Bookings[DateFrom] && [Date]<Bookings[DateTo]))

Added a column:

RoomId-Day = [RoomId]&[Date]

This column is a  link between Room Calendar and Booking Calendar to ID the current status of each day. I used LookUp value to pull the data to biRoomsCalendar

The visual is primarily based on biRoomCalendar.

I used matrix table visual  to display a daily availability of each room. Grouped by room type and added three slicers: Date, Room Type and Current status filters

Colour coding was achieved by conditional formatting.

I have shortened the date to d.m format to narrow the columns

Now we need to be careful to filter one year at the time, otherwise the data from the different years will summarise and we get a faulty visual.

No measures were created for this Viz

A-Available

B- Booked

M - Maintenance

O-Out of Order

V- Void

If you wish to say thank you please donate to the charity I use to work for.

Donate here