Power BI: Find out occupancy using Date Filter(Slicer)

[Anonymous]

I have a scenario where I want to find the occupancy by selecting a date in a slicer

Details are as below

Room Table: Room Id and Room name

Room ID	Room Name
1	R101
2	R102
3	R103
4	R104
5	R105

Occupancy Details Table: Contains the historical data for each room and for the period for which it was occupied.

Room ID	Coustomer ID	Room Name	Start Date	End Date
1	1	R101	Jan-17	Mar-17
1	2	R101	Apr-17	Jun-17
1	3	R101	Jul-17	Sep-17
2	4	R102	Mar-17	Jun-17
3	5	R103	Jan-17	Sep-17
4	6	R104	Apr-17	Dec-17
5	7	R105	Jan-17	Jun-17

Now i want to pass a data and find out which rooms were occupied and vacant on a particular date.

Eg If I pass date as Jul-2017

I will get R101, R103 and R104 as Occupied rooms.

However, While calculating the Vacant Rooms historical records of R101 are also considered.

I want a workaround by which I could exclude the historical records of all the currently occupied Rooms.

Desired output

Date Filter - Jul 2017

Occupied Rooms: R101, R103, and R104

Vacant Rooms: R102 and R105

Please let me know if you need further details and looking forward to your feedback.

Thanks,

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, you need to create a full calendar table. And don't create relationship between Calendar table and this Book table. 

Then create a flag measure to determine if the room is available.

IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)

Then add this measure in to Visual Level Filter.

Regards,

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, you need to create a full calendar table. And don't create relationship between Calendar table and this Book table. 

Then create a flag measure to determine if the room is available.

IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)

Then add this measure in to Visual Level Filter.

Regards,

[Anonymous]

@v-sihou-msft

Thanks, Using the flag to identify the occupancy of the room had resolved my problem.

I have a one follow-up question.

I have created a calculated measure as you have suggested 
IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)

Then I have used this calculated measure as below

Occupant = CALCULATE( If( [IsAvailable]=1 , MAX([Tenant Name]) , MAX([Room Name])))

This Measure displays "Tenant Name" if it is occupied else it will display "Room Name".

This measure works correctly in matrix visuals. However, I am not allowed to use this measure (Occupant)in Treemap as group or

detail values. 

Is this a limitation of Treemap or am I missing something?

Thanks

[v-sihou-msft · Accepted solution]

@Anonymous

The Tree Map should only accept hierarchical data in Group or Detail, which means the values should be "fixed". You can't put a measure. You may try to add measure into Value or Tooltip.

Regards,

[Anonymous]

@v-sihou-msft

It works with Tooltip, not in values (I guess the reason being that measure return text values ), I will try Visio visual to achieve this functionality of highlighting floor/room occupancy.

Thanks for the quick feedback.