Average occupancy

IrisW — Thu, 16 May 2024 10:45:28 GMT

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

Re: Average occupancy

lbendlin — Thu, 16 May 2024 16:11:11 GMT

I just can't manage to show the average per time in the graph.

You need to quantify "time". Per what? Per hour, per 30 or 15 minute slot? Or maybe you didn't mean to say "average", rather "for each point in time" ?

Re: Average occupancy

IrisW — Fri, 17 May 2024 06:41:38 GMT

Yes, I'm sorry. I meant "for each Start time". One court is reserved per 15 minutes. So the starting times could be every 15 minutes.

Re: Average occupancy

lbendlin — Fri, 17 May 2024 16:10:06 GMT

Which locale is this? I can't find any locale that uses 8.30 am notation.

Re: Average occupancy

lbendlin — Fri, 17 May 2024 16:27:46 GMT

topic Re: Average occupancy in DAX Commands and Tips

Average occupancy

Re: Average occupancy

Re: Average occupancy

Re: Average occupancy

Re: Average occupancy