Number of events in time intervals, using start and finish time

[tonedeaff]

Hi All, 

I am stuck on this and would appreciate some help.

I have data for a range of booking events across various rooms which have a start time and finish time. I want to visualise and analyse the volume of bookings across the day - so identifying the number of bookings that occur across a 24hr time period at 30min intervals. 

Sample data:

What I'm hoping to visualise is something like this:

I've attached a PBIX with this example.

Thanks in advance for any help!

[v-jingzhang · Accepted solution]

Hi @tonedeaff 

I add two Dim tables (Dim Date & Dim Hour) to your model and build relationships like below. 

And add three columns with DAX to 'Appointment' table as below. 

Then I can use the following measure to calculate the number of bookings per time period. The sample file has been attached at bottom. 

Measure = COUNTROWS(FILTER(ALLSELECTED('Appointment'),'Appointment'[Start Time Value]<=SELECTEDVALUE('Dim Hour'[Value]) && 'Appointment'[End Time Value]>SELECTEDVALUE('Dim Hour'[Value])))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[stribor45]

@v-jingzhang could this solution be expanded to do exactly same thing only this time to consider based on weekdays as well? On Mondays we have this many sloys available, Tuesday and so on?

[tonedeaff]

Thank you @v-jingzhang ! 😊