Forum Discussion
Calculate Occupancy (%)
Hi all,
I'm trying to come up with a measure to help measure the occupancy of a particular loading location (A & B). Ships go to these locations and they remain docked for a certain time until loading is complete and the ship leaves. I'd like to have a measure that allows me to view the average occupancy of a day of the week, or week of the month, or month of the year (etc) - per location (A or B).
Importantly, I don't just want to calculate occupancy on days when the location is busy, but also include occupancy for days when there are no ships loading. Hope that makes sense - really appreciate any feedback. Needless to say I'm pretty new to PBI.
Here is a sample of the data I'm working with:
Mohs
7 Replies
Familiarize yourself with the "CROSSFILTER(,,NONE)" pattern where you use a Dates/Calendar table against your occupancy table to fill in the gaps in your data. You may want to start small and do that for one location first to see how that works.
A sample result will be a list of dates (from your calendar table) with the number of minutes that location (from your occupancy table) had a ship present. 1440 for a full day, 0 for no occupancy. You'll want to think about how to handle multiple ships per location and day.
Thanks lbendlin - I'll look into your suggestion !
Hi,
Based on that data that you have shared, please let us know the exact result that you are expecting.
Hi Ashish,
An example of the results I'm expecting is listed below. Note that some events span over 2 days (and could be more so occupancy needs to be calculated/split based on calendar date.
Below I'm only showing location A for Aug 14 below but the reports needs to be able to do this per location (A and B) and per day (and week/month etc).
Location A Occupancy
Aug 14: 8%
Aug 15: 43%Explanation: ship arrives Aug 14 22:09 and leaves Aug 15 at 10:13 (total 12 hours 4 minutes). Occupancy is apportioned to Aug 14 (1h 51m/ 24h) and Aug 15 (10h 14m/24h).
Thanks in advance!
Provide some sample data in usable form.
Hi lbendlin ,
See below:Load date Load end Duration (hours) Location Ship Thu Sep-17 00:00 Thu 01:17 1 B SSBBB Wed Sep-16 18:47 Thu 00:59 6 A SS444 Sun Sep-13 16:58 Mon 02:36 10 B SS999 Fri Sep-11 00:01 Fri 20:41 20 A SSQQQ Thu Sep-10 18:24 Thu 23:59 5 A SSQQQ Wed Sep-09 22:32 Thu 14:08 16 A SS444 Tue Sep-08 16:21 Tue 22:04 6 B SS555 Tue Sep-08 05:00 Tue 17:00 12 A SSLLL Mon Sep-07 10:06 Mon 21:40 11 B SS999 Sun Sep-06 00:00 Wed 08:00 80 A SS111 Sun Sep-06 00:00 Sun 00:38 0 A SSMMM Wed Sep-02 19:44 Thu 13:48 18 A SS444 Tue Sep-01 00:41 Tue 19:16 19 B SS555 Tue Sep-01 00:00 Tue 23:59 23 A SS123 Sun Aug-30 11:13 Sun 23:52 12 B SS999 Sun Aug-30 00:00 Sun 23:59 23 A SS987 Wed Aug-26 19:18 Thu 15:30 20 A SS444 Tue Aug-25 23:28 Wed 19:00 20 A SS111 Tue Aug-25 17:54 Tue 23:28 6 B SS555 Mon Aug-24 16:53 Tue 00:39 8 B SS555 Mon Aug-24 00:01 Mon 16:53 16 A SS547 Sun Aug-23 16:06 Sun 22:15 6 B SS999 Sat Aug-22 08:53 Sat 21:42 13 B SS999 Sat Aug-22 08:53 Sun 16:06 32 A SS222 Sat Aug-22 00:00 Sat 00:38 0 B SSBBB Fri Aug-21 14:15 Sat 03:37 13 A SSMMM Fri Aug-21 00:01 Fri 14:07 14 A SSQQQ Thu Aug-20 14:15 Thu 23:59 9 A SSQQQ Wed Aug-19 20:07 Thu 14:07 18 A SS444 Thanks,
Mohs
