Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Unfortunately I haven't yet resolved this issue. Suggestions would be appreciated!
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
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |