Forum Discussion
Average occupancy by location, between two dates
Hi there,
I have a need to calculate the average occupancy of event locations for each event.
I've tried varying combinations of 'CALCULATE', 'FILTER', 'DATESBETWEEN' and have spent a few hours trying to find a solution on this forum.
Events vary greatly in length, but a start and end date is always available.
I have 19 locations to date, with more added regularly.
In 'Table A' I have daily reports of occupancy for each location;
| Date | Location | Occupancy |
| 20/09/21 | Hall | 560 |
| 20/09/21 | Park | 1230 |
| 21/09/21 | Hall | 540 |
| 21/09/21 | Park | 1170 |
In 'Table B' I have start and end dates for each event, and need to calculate the average occupancy in order to calculate further statistics within this table.
| Start Date | End Date | Location | Avg Occupancy |
| 20/09/21 | 21/09/21 | Hall | |
| 20/09/21 | 21/09/21 | Park |
Any assistance the community could provide would be greatly appreciated please!
Avg Occupancy CC =
AVERAGEX (
FILTER (
TableA,
TableA[Location] = TableB[Location]
&& TableA[Date] >= TableB[Start Date]
&& TableA[Date] <= TableB[End Date]
),
TableA[Occupancy]
)
2 Replies
Avg Occupancy CC =
AVERAGEX (
FILTER (
TableA,
TableA[Location] = TableB[Location]
&& TableA[Date] >= TableB[Start Date]
&& TableA[Date] <= TableB[End Date]
),
TableA[Occupancy]
)