cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michael_helland
Regular Visitor

Schedule availability by variable time range

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:

PBI_Event_02.png

 

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.

 

PBI_Event_01.png

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

 

Thanks for your help!

1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: Schedule availability by variable time range

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors