Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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,
For example:
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!
Solved! Go to 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
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
Hi @v-cgao-msft
Many thanks for taking the time to provide this solution. However, I am encountered two issues:
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!
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
88 | |
87 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
83 | |
63 | |
54 |