Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Drewbysnackz
Regular Visitor

Summarizing Capacity Allocated & Capacity Remaining for a Bookable Resource

Hi all - long time lurker, first time poster, so apologies in advance if this is the incorrect sub:

I am hoping to summarize completed/scheduled event duration data with values from three tables.

table A - Events: Contains running registry of training events.

  • Calculated columns use DATEDIFF to return event duration (event end date/time - event start date/time) in hours and minutes

table B - Trainer Logs

  • Maps the user guid of a bookable resource (in this case, a contact with a "Trainer" designation) to the guid of the event in table A in which that Trainer's time was used

table C - Trainer Capacity

  • Trainer's name, guid, and a hard-coded quarterly hours value (160)

 

The goal is relatively straightforward:
For each unique trainer ID + trainer name in table B/C, summarize:

  • the QTD count of Completed vs. Scheduled events (from table A).
    • Any event with a end date < today = "Completed"; with start date > today =  "Scheduled"
  • the duration in hours tied to those Completed/Scheduled events (table A)
  • the sum of hours completed QTD + hours Scheduled QTD (let's call it "Hours Booked or Completed")

Deducting the "Hours Booked or Completed" value from the hard-coded maximum allowable hours (160) that any one trainer has available in any one quarter should then allow me to arrive at a % of Quarterly Hours Used value, which would prove useful when answering the question of which trainers have the capacity remaining to book them with future events.

 

I contemplated bringing the trainer_id value (table B) into the events table (table A) via LOOKUPVALUE, but was unsure if there was a more elegant solution that virtualized this relationship.

Lastly, it's worth noting that an event can have multiple trainers assigned to it. In these instances, I'd like to avoid counting a single event with duration = 8 hours and >1 trainer assigned as 8 hours consumed by each assigned trainer, such that the duration of the event is split by the number of trainers assigned, i.e.,:

If duration = 8 hours && assigned trainer count = 1, then deduct 8 hours from trainer 1's capacity
If duration = 8 hours && assigned trainer count = 2, then deduct 4 hours from trainer 1's capacity and 4 from trainer 2's capacity
If duration = 8 hours && assigned trainer count = 3, then deduct 2.67 hours from t1, 2.67 hours from t2, 2.67 from t3
...etc.

Link to sample data below. Thanks

sample data

EDIT: Public link




1 REPLY 1
lbendlin
Super User
Super User

ok. what is your question?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors