Calculating desk usage figures

Good afternoon everyone,

I'm trying to create some visualisations in Power BI for data I've been provided from a desk booking application. The application generates the following example data:

Desks Table

Bookings Table

Expected Result (in a table visualisation)

Where,

• Weekdays Booked = the number of weekdays where at least one booking has been made per day. Multiple bookings on one day do not count as multiple bookings. If a desk has an 'Active From' date populated in the Desks Table, the number of weekdays is calculated from this date up to the current date. If the 'Active From' date is blank in the Desks Table, the number of weekdays is calculated from the first booking found in the Bookings Table for the desk up to the current date. Bookings on weekends and bookings in the future are ignored.
• Maximum Weekdays = the maximum number of possible weekdays between the 'Active From' date (if not blank) and the current date. If the 'Active From Date' is blank, calculated from the first booking found in the Bookings table for the desk up to the current date.

For example:

• Desk 1A is 'Active From' 21/08/2024. There are 6 weekdays (inclusive) between 21/08/2024 and 28/08/2024 (todays date). There are two bookings for Desk 1A. Only one of these bookings is on a weekday, therefore, the overall percentage usage for this desk is 1/6 = 16.67%.
• Desk 1C has no 'Active From' date. The earliest booking for this desk is on 27/08/2024. There are 2 weekdays (inclusive) between 27/08/2024 and 28/08/2024 (todays date). There are two bookings on this day, however, I am only interested if the desk has been booked at least once per day. Therefore, the overall percentage usage for this desk is 1/2 = 50%.
• Desk 1B is 'Active From' 21/08/2024. There are 6 weekdays (inclusive) between 21/08/2024 and 28/08/2024 (todays date). There is a single booking for Desk 1B, however, it is in the future so is ignored in the calculation. Overall percentage usage for this desk is 0/6 = 0%.

I've already loaded this data into Power BI and established a relationship between the Desks Table and Bookings Table by the Desk ID. However, I am struggling to write the measures/calculated columns required to generate the percentage usage stats I need.

Sorry if this is overly complicated but I am happy to clarify if any further detail is required.

I am also looking at a way of calculating usage on an hour by hour basis between 'core hours' in addition to the requirement above, however, I appreciate this is a lot more complicated. Lets say I wanted to know how often a desk is occupied on a daily basis (weekdays only) between the hours of 09:00 and 15:00, how would I go about achieving this in addition to the above?

Thank you in advance for anyone who is able to provide a solution to the above - I have been scratching my head on how to achieve this for the past few weeks so any insight would be greatly appreciated!