Events in progress per hour slot aggregated across multiple days

[Anonymous]

Hi guys,

I am looking at booking data of a number of assets in the following format:

Asset ID	Booking ID	started	finished	started_hour	finished_hour
41	5417	06/09/2020 18:47:28	06/09/2020 19:22:07	06/09/2020 18:00:00	06/09/2020 19:00:00

Important to know: Bookings can stretch over more than 1 day.

What I would like to have as an output is a measure that counts all ongoing bookings aggregated for every hour slot over a certain period of time.

The desired output (in table form) would look something like this:

started_hour	finished_hour	Count bookings in progress
08:00	09:00	5
09:00	10:00	4
...	..	..

I have managed to count the bookings in progress per hour slot for every day individually, but not aggregated across multiple days. Do you have any ideas how to do that?

Cheers,

Lukas

[Greg_Deckler]

@Anonymous Have a look at Open Tickets - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

If you could share more sample data as text could probably whip up a version of this for you.

[Anonymous]

Thanks for the link. From what I can see this solution allows you to filter on the number of open tickets across any date range. What I would like however is to count the open 'tickets' (in my case bookings) independet of the date and only based on the time dimension. Please let me know if I missed something.

[Anonymous · Accepted solution]

Quite different than I thought,

but this thread provides a solution by expanding every booking into hour block:

https://community.powerbi.com/t5/Desktop/Calculating-hourly-occupancy-of-a-medical-clinic-based-on-a...

Cheers,

Lukas

[Fowmy]

@Anonymous 

Create a disconnected Table with Start and End hour bracket for 24 Hours. Create a measure to count records that fall within the range of the hours in the disconnected table.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks - I guess that works in case bookings wouldn't span across multiple dates