Forum Discussion
Monthly Occupancy Figures for Properties
Hello, i'm trying to create a matrix table showing the occupancy levels for some properties however I can't get it to work.
Here is a sample of the dataset. There are multiple tenancies for each room and all have a start and end date.
| Property | Tenancy Start Date | Tenancy End Date | Tenancy Length |
| 1 High Street | 01/01/2023 | 10/02/2023 | 40 |
| 1 High Street | 25/02/2023 | 28/02/2023 | 3 |
| 1 Long Road | 01/02/2023 | 07/02/2023 | 6 |
| 1 Long Road | 15/02/2023 | 21/02/2023 | 6 |
| 2 High Street | 20/12/2022 | 10/01/2023 | 21 |
| 2 High Street | 11/02/2023 | 10/03/2023 | 27 |
| 2 Long Road | 09/01/2023 | 17/01/2023 | 8 |
| 2 Long Road | 03/02/2023 | 10/03/2023 | 35 |
| 3 High Street | 02/02/2023 | 22/02/2023 | 20 |
| 3 High Street | 28/02/2023 | 18/03/2023 | 18 |
| 3 Long Road | 02/02/2023 | 11/02/2023 | 9 |
| 3 Long Road | 23/02/2023 | 04/03/2023 | 9 |
I want to create a table like the one below which shows how many days each property was occupied during the month.
| Property | Jan-23 | Feb-23 | Mar-23 |
| 1 High Street | 31 | 14 | |
| 1 Long Road | 14 | ||
| 2 High Street | 10 | 18 | 10 |
| 2 Long Road | 8 | 25 | 10 |
| 3 High Street | 20 | 18 | |
| 3 Long Road | 14 | 4 |
Does anyone have any suggestions on how to do this?
Thanks in advance.
2 Replies
- amitchandak
Super User
g_hill , One of the two ways should help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Allocate data between Range: https://youtu.be/O653vwLTUzM
Thanks for the suggestions Amit, however they don't quite achieve what i'm looking for.
