Calculate Occupancy (%)

[Mohs]

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

[Mohs]

Unfortunately I haven't yet resolved this issue. Suggestions would be appreciated!

[lbendlin]

Provide some sample data in usable form.

[Mohs]

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

[Ashish_Mathur]

Hi,

Based on that data that you have shared, please let us know the exact result that you are expecting.

[Mohs]

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!

[lbendlin]

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.

[Mohs]

Thanks @lbendlin - I'll look into your suggestion !