Schedule availability by variable time range

[michael_helland]

Hi,

I’ve been stuck on this for a couple of days and I’m sure I’m missing something obvious so I’m hoping someone can point me in the right direction here before my forehead becomes completely flat. 🙂

I’m trying to show the number of people that are scheduled in a given time slice that is variable (i.e. hour, half hour, quarter hour) increments. It’s very similar to http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat... but at a more granular level. I can do it at a day level but below that I get stuck since I can show when the event started by the time ranges but not for each of the sub time ranges. For example an event that starts at 10:00 AM and goes until 2:00 PM should be counted for each of the half hour slots between those times and not just at the 10:00 AM slot if I show by half hour.

Here's what I want to end up with:

Here’s the Event table:

The Event table has a list of events of variable start/end dates/times (15 minutes in length up to 24 hours) for a variable number of people.

I have standard Date and Time dimensions as well that I'm pulling in as well.

Thanks for your help!

[v-qiuyu-msft]

Hi @michael_helland,

From @OwenAuger suggestion in the link, I think you also need to create time table which like calendar table(which divides period between start time and end time based on the interval to many records. Assume start time is 10:00 am and end time is 11:30 am, if interval is 30 minutes, there should be three records in table(10:00 am,10:30 am,11:00 am), then create relationship between time table and fact table.

Best Regards,
Qiuyun Yu