cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aganes Frequent Visitor
Frequent Visitor

Show Utilization Graph Calculated from Start and End Time

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]

Utilization Capture.PNG

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

4 REPLIES 4
CheenuSing Super Contributor
Super Contributor

Re: Show Utilization Graph Calculated from Start and End Time

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Community Support Team
Community Support Team

Re: Show Utilization Graph Calculated from Start and End Time

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

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



For learning resources/Release notes, please visit: | |
aganes Frequent Visitor
Frequent Visitor

Re: Show Utilization Graph Calculated from Start and End Time

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

 

 

 

 

 

Community Support Team
Community Support Team

Re: Show Utilization Graph Calculated from Start and End Time

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

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



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,935)