Hi, I have a challenge in Power BI.
I have a table (name is "Export") containing the following information:
- date
- player
- start time (= [Reservering Tijd start])
- end time (= [Reservering Tijd t/m])
- reservation number
- tennis court (= [Baan])
For examble:
Date: Player: Start time: End time: Res.nr. Court
16-5-2024 Mike 8 am 9 am 001 1
16-5-2024 Steve 8 am 9 am 001 1
16-5-2024 John 8 am 9 am 001 1
16-5-2024 Robin 8 am 8.30 am 002 2
16-5-2024 Rick 8 am 8.30 am 002 2
17-5-2024 Patrick 8 am 9 am 003 1
17-5-2024 Dennis 8 am 9 am 003 1
On May 16 at 8 a.m. the court occupancy is 2. On May 16 at 8.45 a.m. the court occupancy is 1 (just Mike, steve and John).
On May 17 at 8 a.m. the court occupancy will be 1.
On average I have an occupancy of 1.5 at 8 a.m (sometimes 1 court, other times 2 courts) and at 9.15am an average occupancy of 1 (at 9.15am a maximum of 1 court is occupied.)
I also have a time table (name is 'Timetable') so that I have all possible times between 8am and 11pm ready.
I want to make a graph showing the average tennis court occupancy at each time. So on the x-axis all times from 8:00 am to 11:00 pm and on the y-axis the average tennis court occupancy.
I managed to do this by adding the following measurement to muy Export-table:
Baanbezetting =
CALCULATE(DISTINCTCOUNT(Export[Baan]),
FILTER(values(Export[Reservering Tijd start]),Export[Reservering Tijd start]<=max(TimeTable[Tijd])),
FILTER(values(Export[Reservering Tijd t/m]),(Export[Reservering Tijd t/m]>=min(TimeTable[Tijd]))))
If I select a number of dates (for example, all dates in April), it will display a full tennis court occupancy (of 100%) at 8 p.m. (busiest time) because in that date range all runways were occupied somewhere at 8 p.m. This should be about 95%.
I just can't manage to show the average per time in the graph.
Thanks for your thoughts.
Iris
