Calculate Occupancy per room type and free rooms per date.

[n3ptune]

I have sales  table with room bookings (hotel), i have also another room type table with the name and the number of each room type.

I have calculated the occupancy per room type with the formula 

IF(ISBLANK(CALCULATE(DIVIDE([Total_rooms_booked];[Rooms_type_available_2020]);Date_Rev;Date_Rev[Year]>2019));0;CALCULATE(DIVIDE([Total_rooms_booked];[Rooms_type_available_2020]);Date_Rev;Date_Rev[Year]>2019))

then i made a new one so the matrix tables shows correct sum 

OCC %_2020_RTperDate = var _table = SUMMARIZE(Room_type_Qty;Room_type_Qty[Room_type];"occ20perrtyp";[OCC %_2020_perRT])

return

IF(AND(HASONEVALUE(Room_type_Qty[Room_type]);(HASONEVALUE(Date_Rev[Day])));[OCC %_2020_perRT];AVERAGEX(_table;[occ20perrtyp]))

but still the sum is not correct it's missing 4 or 5 rooms always from the calculation.

And then i want to calculate free rooms. if no room has been booked then it has to show the number of available rooms,  but many times it shows blank cells.

free rooms = CALCULATE([Rooms available_2020]-[Total_rooms_booked 2020];FILTER(Date_Rev;Date_Rev[Year]=2020))

how can i correct the blank cells?

Room types tablematrix table with occupied, free rooms ignor stop

[amitchandak]

If 0 expected then try

free rooms = CALCULATE([Rooms available_2020]-[Total_rooms_booked 2020];FILTER(Date_Rev;Date_Rev[Year]=2020))+0

[n3ptune]

yes it fixed the blank cells , but it doesnt show me the correct empty rooms just 0.

so i changed the formula to

Free_room_types =

var currentyear = MAX(Date_Rev[Year])

var freeroomtypes = CALCULATE([Rooms_type_available_2020]-[Total_rooms_booked];FILTER(Date_Rev;Date_Rev[Year]=currentyear))+0

var maxpertype = [maxpertype]

return

if (ISBLANK(freeroomtypes);maxpertype;freeroomtypes)

and maxpertype = average[number_r] from the room type table

check the zero values, instead of number of each room type as occupancy is 0

[n3ptune]

the problem starts from here the room type table is  this one

so i create a measure called maxpertype = average(number_r) 

then there is an active relationship with the bookings table with room type to room type one to many.

so when i calculate the room types number available   for

2020 = 

CALCULATE(DISTINCTCOUNT(Date_Rev[Date].[Day]) * [maxpertype];Date_Rev[Year]>2019)

it works for the rooms that has already booked and there is a transaction inside the sales table.

for pres sui and db2eco there are no transactions , so this is the look now.

for the others months its working as you can see tha variation between months with 30 and 31 days..

[v-frfei-msft]

Hi @n3ptune ,

kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[n3ptune]

@v-frfei-msft thank you for replying

ill try to explain it better

so we have this table which is for room types

and this measure maxpertype = average(number_r)  so i can use the numbers for my calculations

 then we have sales table which is like     15/01/20      1(for occupied)     sui(room type)   200(price)

                                                                  16/01/20      1                            sui                     200

there is active relationship room types to sales table.

i want to create a table which will have occupancy per room types and free rooms per room type at rows

and date.day at columns. i have month filter slicer  so i can choose the month i want to visualize.

measure that affect the calculations are.

Rooms_type_available_2020 = CALCULATE(DISTINCTCOUNT(Date_Rev[Date].[Day]) * [maxpertype];Date_Rev[Year]>2019)

Total_rooms_booked 2020 = CALCULATE(SUM(sales[occ]);Date_Rev[Year] = 2020)

so

1.occupancy per room type =

CALCULATE(DIVIDE([Total_rooms_booked];[Rooms_type_available_2020]);Date_Rev;Date_Rev[Year]>2019)

this formula works for the most of days and room types , except those that there isnt any entry at sales table for specific dates  and it shows blank cells.

2.free rooms per type

CALCULATE([Rooms_type_available_2020]-[Total_rooms_booked];FILTER(Date_Rev;Date_Rev[Year]=2020))

so i get the correct results only at the dates that the previous measure occ per rtype works correct.

for the blank cells of occupancy measure i got blank cells also for free rooms per type as you can see from my pic.