calculating daily occupany rate

[ninakarsa]

Hi

I am trying to find away to calculate the number of occupied spaces for a given day by parking area. For example, based on the arrival and departure dates how many customers have an occupied parking spot on a given day. And then to be able to drill down to view the chart/table by parking area.

[az38]

Hi @ninakarsa 

Your question is very complex and, obviously, there is not enough source data to give you a full and complex answer. but i try

first, the best practice is to create Calendar table for your task.

you could do this by create a calculated table like this

CalendarTable = CALENDARAUTO()

or

CalendarTable = CALENDAR (DATE (2019, 1, 1), DATE (2019, 12, 31))

next, you can create a dimension table 'Parking Areas' which define all reported areas (one area = one row in the table).

next, you can create a table CalendarAreasTable that will be looked like

Date	Parking Area
01.01.2019	Zone A
01.01.2019	Zone B
01.01.2019	Zone C
02.01.2019	Zone A
02.01.2019	Zone B
02.01.2019	Zone C
03.01.2019	Zone A
03.01.2019	Zone B
03.01.2019	Zone C

to do that create a new calculated table

CalendarAreasTable = CROSSJOIN(CalendarTable;'Parking Areas')

next you can add a simple measure to your new CalendarAreasTable:

Measure = 
CALCULATE(COUNTROWS('DataTable'),
FILTER(ALL('DataTable'), 'DataTable'[Departure Date]<=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Departure Date]>=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Parking Area]=SELECTEDVALUE('CalendarAreasTable'[Parking Area]))
)

where 'DataTable' is a table with log of your parking records

So, this measure will give you quantity of customers for each day by each Area and you could drill down it easily

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[ninakarsa]

Thanks for your quick response, so I will attached below the tables I have and what i want to do:

So Table A has the booking information, table b has the info that i want to drill by and table c is the calender

Table A: Bookings

Customer ID	adate	ddate	car park	car park area
123	01/05/2019	05/05/2019	Left	Zone A
124	02/05/2019	04/05/2019	right	Zone B
125	03/05/2019	05/05/2019	Left	Zone C
126	04/05/2019	06/05/2019	right	Zone A
127	01/05/2019	03/05/2019	Left	Zone B
128	02/05/2019	04/05/2019	Left	Zone C
129	03/05/2019	05/05/2019	Left	Zone A
130	01/05/2019	03/05/2019	Left	Zone B
131	02/05/2019	04/05/2019	Left	Zone C
132	03/05/2019	05/05/2019	Left	Zone A
133	01/05/2019	03/05/2019	Left	Zone B
134	02/05/2019	04/05/2019	Left	Zone A
135	03/05/2019	05/05/2019	right	Zone B
136	05/05/2019	07/05/2019	right	Zone C
137	06/05/2019	08/05/2019	right	Zone A
138	04/05/2019	06/05/2019	right	Zone A
139	05/05/2019	07/05/2019	left	Zone B
140	06/05/2019	08/05/2019	left	Zone C
141	08/05/2019	10/05/2019	left	Zone A
142	09/05/2019	11/05/2019	left	Zone B
143	07/05/2019	09/05/2019	left	Zone C
144	08/05/2019	10/05/2019	left	Zone A
145	09/05/2019	11/05/2019	left	Zone B
146	11/05/2019	13/05/2019	left	Zone C
147	12/05/2019	14/05/2019	left	Zone A
148	10/05/2019	12/05/2019	left	Zone B
149	11/05/2019	13/05/2019	left	Zone A
150	12/05/2019	14/05/2019	left	Zone B

Table B: Info

car park	car park area
Left	Zone A
right	Zone B
	Zone C

Table C: Calendar

Table 😧 

crossjoin(calendar,Info)

Then I use the following calcuation to give me the number of occupied spaces on a given day: but this is not returning any results

Measure =

CALCULATE(COUNTROWS(bookings),

FILTER(ALL(bookings), bookings[adate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]))

)

[az38]

hi @ninakarsa 

it seems you're confused marks < and >

try

Measure =
CALCULATE(COUNTROWS(bookings),
FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[ninakarsa]

@az38 @this still does not work. I am new to powerbi, so I am not understanding the issue.

Thanking you in advance

[az38]

@ninakarsa 

for me it works. please, share your pbix-file

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ninakarsa]

@az38 how can i share my pbix file with you?

[az38]

@ninakarsa

use

 https://uploadfiles.io/

and link to ptivate message if data is confidential

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ninakarsa]

@az38 

I have uploaded it. and here is the link https://ufile.io/gq5sanqm

[ninakarsa]

@az38  this seems to be working when i create a 'table' visual but when i create a matrix visual the data displays as below. Also how do i calculate the totals for this? Is there a way to create a table in powerbi with the result shown below?

[az38]

@ninakarsa 

to display all the data in the matrix visual press "Expand" button (see below). 

and try a measure for total correct caluclation

Measure = 
CALCULATE(COUNTROWS(bookings);allexcept(Bookings;Bookings[car park area]);
FILTER(Bookings; bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[ninakarsa · Accepted solution]

@az38 What i want to be able to see is by date the occupied spaces and to be able to drill into zone as below. And to have the total displayed. Thanking you in advance

The table below shows the correct values for zone, but not for left, right. The measure used for this is: 

Measure 5 = CALCULATE(COUNTROWS(bookings), FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area])) )

If I use measure 8, I get the correct values for car park (left/right) but there is no value displayed for zone.

Measure 8 = CALCULATE(COUNTROWS(bookings), FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]) && bookings[car park]=SELECTEDVALUE(CalendarAreasTable[car park]) ) )

[az38]

@ninakarsa 

the same measure as i told you before

Measure = 
CALCULATE(COUNTROWS(bookings),allexcept(Bookings;Bookings[car park area]),
FILTER(Bookings, bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)

there is a pbix-file https://ufile.io/l0a2orpl

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[ninakarsa]

@az38thank you so much for your help...this worked perfectly. I have one more question regarding joining queries in powerbi, if you can help

SELECT A.carpark, A.carparkarea,location, B.ARRDATE, B.DEPDATE, c.amount FROM table1 A
JOIN table2 B ON B.RESID=A.RSID
JOIN table3 C ON C.RESDETAILID=A.RSDETAILID
JOIN table4 D ON A.NAME=D.NAME

[az38]

Hi @ninakarsa 

what do you mean with this sql-statement?

there are 3 good options to make join in power bi:

1. Merge option in Power Query Editor mode

2. Create relationships (you've got a simple one-parameter joins, as i see)

3. Join functions in DAX https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax

and dont forget set posts with solutions as solution for future users, please 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ninakarsa]

@az38Hi, you have been a great help. Following, up with this, i need to calculate % occupied spaces (occupied/total spaces). The table below shows the total spaces by car park and car park area. How can I use dax to get the following result:

	01/05/2019
	occupied spaces	total spaces	occupancy
Zone A	1	30	3.3%
Left	1	10	10.0%
Right		20	0.0%
Zone B	3	45	6.7%
Left	3	30	10.0%
Right		15	0.0%
Zone C
Left
Right

Car Park	Car Park Area	From Date	To Date	Spaces
Zone A	Left	01/05/2019	20/05/2019	100
Zone A	Right	01/05/2019	20/05/2019	50
Zone B	Left	01/05/2019	20/05/2019	320
Zone B	Right	01/05/2019	20/05/2019	150
Zone A	Left	01/06/2019	20/06/2019	150
Zone A	Right	01/06/2019	20/06/2019	75
Zone B	Left	01/06/2019	20/06/2019	340
Zone B	Right	01/06/2019	20/06/2019	150

[az38]

@ninakarsa 

I do not understand how do you calculate total space and how it connects with Spaces from the second table

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ninakarsa]

@az38 

so lets say we have a table with information regarding to the car park table A. Table B, is a calcuated table showing by date customers in the car park area, so for example on the 01/05/2019 1 customer was in car park zone A & left car park area, 4 customers were in car park zone B with 3 in left car park area and 1 in right car park area. And then want to combine the two tables to say that when Date from table b is between from date and to date in table A and car park and car park area from B is equal to car park and car park area from table a then return the number of spaces in table B. This should the return table C 

And finally to create a measure which will return results in table d

Table A

Car Park	Car Park Area	From Date	To Date	Spaces
Zone A	Left	01/05/2019	20/05/2019	10
Zone A	Right	01/05/2019	20/05/2019	20
Zone B	Left	01/05/2019	20/05/2019	30
Zone B	Right	01/05/2019	20/05/2019	15
Zone A	Left	01/06/2019	20/06/2019	15
Zone A	Right	01/06/2019	20/06/2019	60
Zone B	Left	01/06/2019	20/06/2019	35
Zone B	Right	01/06/2019	20/06/2019	70

Table B:

Date	CustomerID	CarPark	CarPark Area
01/05/2019	123	Left	ZoneA
01/05/2019	127	Left	ZoneB
01/05/2019	130	Left	ZoneB
01/05/2019	133	Left	ZoneB

Table C:

Date	CustomerID	CarPark	CarPark Area	Spaces
01/05/2019	123	Left	ZoneA	10
01/05/2019	127	Left	ZoneB	30
01/05/2019	130	Left	ZoneB	30
01/05/2019	133	Left	ZoneB	30

table 😧

	01/05/2019
	occupied spaces	total spaces	occupancy
Zone A	1	30	3.3%
Left	1	10	10.0%
Right		20	0.0%
Zone B	3	45	6.7%
Left	3	30	10.0%
Right		15	0.0%
Zone C
Left
Right

[az38]

@ninakarsa 

maybe you just need to do smth like for calculate occupied places?

Measure Spaces = 
CALCULATE(SUM('Table A'[Spaces]),allexcept(Bookings;Bookings[car park area]),
FILTER(Bookings, bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)

then divide

occupancy = divide([Measure], [Measure Spaces])

where measure - is a measure from above solution

do not hesitate to give a kudo to useful posts and mark solutions as solution