Show Utilization Graph Calculated from Start and End Time

[aganes]

Hi PBI pros,

My company rents out rooms by the hour. I am trying to add a feature to my PBI utilization tracker that shows popularity by half-hour 

We have a system that stores bookings with the following info:

• Booking ID
• Booking Start Date/Time
• Booking End Date/Time

Through a series of calculated columns, I was able to track half-hours of utilization between the start and end time. (Ex: A booking with start time of 3:00 PM and end time of 4:30 PM would be tallied for 3:00 PM, 3:30 PM, 4:00 PM)

[See screenshot]

In the screenshot, we can see utilization merged by month.

What I am now trying to do is display the information vertically and ordered from most-used to least-used, while still tying it to a date range. Based on the screencap above, this data would ideally look like:

3:30 PM --- 6,819

2:00 PM --- 6,745

2:30 PM --- 6,674

1:30 PM --- 6,642

.

.

.

etc.

My hope is to display the top 10 most utilized half-hour blocks as a vertical bar graph, but the issue is that I cannot capture the data in one measure. Any help would be greatly appreciated!

Some additional info about the dashboard:

• The bookings table is DirectQuery from SQL.
• A calendar table is linked to the bookings table by booking date.

Thank you,

Alex

[aganes]

Hi @CheenuSing and @v-shex-msft,

Thank you for your responses. Please click here to find my sample data for the dashboard for the month of August 2018. There's a .pbix in import mode, and an Excel with the data for the three source tables.

I am trying to replicate what is currently the 'Popularity by Hour' bar graph on the 'Utilization Dashboard' tab, but captured with a measure rather than each column being a separate value column. I believe this is necessary so I can manipulate the data more intuitively (specifically removing zeros, sorting by value and using a 'Top 10 feature').

Please see the 'Popularity by Half-Hour' tab for some more context on how utilization is being captured in units.

Any help would be greatly appreciated!

All the best,

Alex

[v-shex-msft]

Hi @aganes ,

I'd like to suggest you to do unpivot column on your time fields to convert records.

Unpivot columns (Power Query)
Then you can write formula to calculate based on unpivoted attribute and values.

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @aganes ,

I'd like some some sample data for test.

How to Get Your Question Answered Quickly

In addition, you can also take a look at following link about analytics across multiple date fields, but it seems not works on direct query mode and will generate huge amount of records.

Spread revenue across period based on start and end date, slice and dase this using different dates

Regards,

Xiaoxin Sheng

[CheenuSing]

Hi @aganes ,

How do you filter the period is it through a slicer.

Can you upload sampledata, pbix and output expected to Google/One Drive and share the link here to find a solution.

Cheers

CheenuSing