Calculating desk usage figures

[XenDance]

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

Desk ID	Active	Active From
1A	TRUE	21/08/2024
1B	TRUE	21/08/2024
1C	TRUE
2A	TRUE	26/08/2024
2B	FALSE

Bookings Table

Booking ID	Desk ID	Date	Time From	Time To
1	1A	21/08/2024	08:00	16:00
2	1A	24/08/2024	09:00	13:00
3	1B	07/09/2024	10:00	16:00
4	2A	21/08/2024	08:00	13:00
5	2A	28/08/2024	11:00	14:00
6	1C	27/08/2024	08:00	14:00
7	1C	27/08/2024	16:00	18:00

Expected Result (in a table visualisation)

Desk ID	Active	Active From	Weekdays Booked	Maximum Weekdays	Usage
1A	TRUE	21/08/2024	1	6	16.67%
1B	TRUE	21/08/2024	0	6	0%
1C	TRUE		1	2	50%
2A	TRUE	26/08/2024	1	3	33.33%
2B	FALSE		0	0

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!

[XenDance]

Hi @v-cgao-msft,

This is perfect, thanks for providing this solution so quickly 😀

[v-cgao-msft]

Hi @XenDance ,

Please create 2 new measures:

Maximum Weekdays = 
VAR __today = TODAY()
VAR __cur_active_from_date = SELECTEDVALUE('Desks'[Active From])
VAR __active_from_date = IF(ISBLANK(__cur_active_from_date), MAX('Bookings'[Date]) , __cur_active_from_date)
VAR __result = IF(ISBLANK(__active_from_date), 0, NETWORKDAYS(__active_from_date,__today,1))
RETURN
__result

Weekdays Booked = CALCULATE(COUNTROWS('Bookings'),FILTER('Bookings', NOT WEEKDAY('Bookings'[Date],2) IN {6,7})) + 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[XenDance]

Hi @v-cgao-msft 

Many thanks for taking the time to provide this solution.  However, I am encountered two issues:

• I failed to mention in my initial post that there are instances where the 'Active From' date in the desks table is a date in the future.  This throws my calculation off since I get a negative number for the purposes of calculating the percentage.  Is it possible to amend this measure so it treats 'Active From' dates in the future the same as a blank date (i.e. maximum weekdays should equal 0)?
• The weekdays booked figure includes bookings made in the future (i.e. greater than todays date).  I would like to disregard these for the purposes of the calculation.  Apologies if I did not make this clear in my initial message!

Other than these issues, this is exactly what I was looking for - thank you for spending the time to look at this.  I'm hoping it is a relatively minor change (I'm a bit lost when it comes to looking at the DAX you provided) so I appreciate any further assistance you can provide!

[v-cgao-msft · Accepted solution]

Hi @XenDance ,

Okay, I've modified the conditions.

Maximum Weekdays = 
VAR __today = TODAY()
VAR __cur_active_from_date = SELECTEDVALUE('Desks'[Active From])
VAR __active_from_date = IF(ISBLANK(__cur_active_from_date), MAX('Bookings'[Date]) , __cur_active_from_date)
VAR __result = IF(ISBLANK(__active_from_date) || __today<__cur_active_from_date, 0, NETWORKDAYS(__active_from_date,__today,1))
RETURN
__result

Weekdays Booked = CALCULATE(COUNTROWS('Bookings'),FILTER('Bookings', NOT WEEKDAY('Bookings'[Date],2) IN {6,7} && 'Bookings'[Date]<=TODAY())) + 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum