Monitoring Flex desk availability based on dynamic start/endDate

[kerre]

Hi ,

I'm at very beginning of designing a "flex desk reservation" application by means of Power BI, Power Automate and Power App. Before getting to automating leg of the project, I'd like to solve some problems with dummy data.

My problem is to monitor available flex desks within the date range one would like to make a new reservation. Some practical information:  There are 5 Tables  and model is as follows.

1) The Start/End Date tables are used for choosing the date range one reserves a flex table: it can be only one day (Start Date = end Date) or for multiple subsequent days ( Start Date < End Date ) 

2) Desks Table has attributes like DeskNo, Floor, Flex(Yess,No) etc.

3) Users Table has attributes such as First Name, Last Name, Full Name, Emp.ID etc.

4)Reservation Table (basically the fact table) has the log of reservations: DeskNo, Reserved by(User Full Name), Start Date, End Date

I have 2 measures that holds the Requested reservation Dates:

Selected Value Start Date = 'Start Date'[Date]

Selected Value Start Date = 'End Date'[Date]

My Reservations Table (Fact Table):

Reserved by --  Desk -- Start Date   ---  EndDate

CR                    107        01/09/2021       02/09/2021
DM                   122        03/09/2021       03/09/2021

DM                   153        08/09/2021       10/09/2021

KDS                  200        06/09/2021       07/09/2021

KDS                  202        12/09/2021       12/09/2021

KY                    107         06/09/2021       07/09/2021

KY                    122         13/09/2021       14/09/2021

NH                   105         01/09/2021       01/09/2021

NH                   107         03/09/2021       06/09/2021

NH                   107         14/09/2021       14/09/2021

So I wrote a measure to use in filtering available and occupied desks:

(F)Filter Occupied Desks =

                          IF (MAX ( Reservations[StartDate] ) <= [Selected Value End Date]

                                           && MAX ( Reservations[EndDate] ) >= [Selected Value Start Date],1,0)

When I select as follows:

Start Date: 03/09/2021

End Date : 03/09/2021 

Basically there are 2 occupied desks, 122 and 107, however they are also flagged "available" according to other 2 records, so when I use my measure for filtering  Occupied Desks table visualisation (includes all desks in the building) I get no ocuppied desks, although desk 107 and 122 are occupied withing the choosen dates:

Reserved by --  Desk -- Start Date   ---  EndDate

CR                    107        01/09/2021       02/09/2021  flags "available"
DM                   122        03/09/2021       03/09/2021 flags "occupied" 

DM                   153        08/09/2021       10/09/2021

KDS                  200        06/09/2021       07/09/2021

KDS                  202        12/09/2021       12/09/2021

KY                    107         06/09/2021       07/09/2021

KY                    122         13/09/2021       14/09/2021 flags "available"

NH                   105         01/09/2021       01/09/2021

NH                   107         03/09/2021       06/09/2021 flags "occupied"

NH                   107         14/09/2021       14/09/2021

   

While I choose some other dates:

Start Date : 08/09/2021

End Date: 09/09/2021

There is only one record within this dates and this desk has also only one record in my fact table so I can see desk 153 in the filtered Occuppied Desks table visualisation:

Reserved by --  Desk -- Start Date   ---  EndDate

CR                    107        01/09/2021       02/09/2021
DM                   122        03/09/2021       03/09/2021

DM                   153        08/09/2021       10/09/2021  flagged "occupied"

KDS                  200        06/09/2021       07/09/2021

KDS                  202        12/09/2021       12/09/2021

KY                    107         06/09/2021       07/09/2021

KY                    122         13/09/2021       14/09/2021

NH                   105         01/09/2021       01/09/2021

NH                   107         03/09/2021       06/09/2021

NH                   107         14/09/2021       14/09/2021

I tried to change my filter measure so that if a desk is flagged as 1 ("occupied") in one record and flagged as 0 ("available") on an other, it'd be flagged always as 1 "occupied". My new measure bacame like:

(F)Filter Occupied Desks = MAXX(SUMMARIZE(Reservations, Reservations[Desk], "Occupation",

                                                                            IF (MAX ( Reservations[StartDate] ) <= [Selected Value End Date]

                                                                            && MAX ( Reservations[EndDate] ) >= [Selected Value Start Date],1,0)),                                                                           [Occupation])

I also thought to create a table with SELECTCOLUMNS() where I first flag each record by the If statement above and group and sum up occupation flags by SUMMARIZE() function so that if a desk is once flagged occupied its sum of occupation level would be always above 0 but this hasn't given either any desirable result as filter on Occupied Desks table visuaization.

Community's help is much appriciated.

Thanks in advance 

Kerre

[amitchandak]

@kerre , Try to use an indpendent dat table as slicer and then try meausre like

measure =
VAR _max = MAXX(allselected("Date"),"Date" [Date])
var _min = MinX(allselected("Date"),"Date" [Date])
return
IF (Min ( Reservations[StartDate] ) <= _min
&& MAX ( Reservations[EndDate] ) >= _max,1,0)

[kerre]

Thanks @amitchandak  for your reply.

I actually have 2 independent Date tables named Start Date and End Date of which selected values are hold in 2 measures:

Selected Value Start Date = 'Start Date'[Date]

Selected Value Start Date = 'End Date'[Date]

I have implemented your formula as follows but that returns now no 1's ("ocuppied") at all. e.g. I choose whole month September in which there should deffinitely be 6 occupied desks.

(F)Filter Occupied Desks =

                    var _max = MAXX(allselected('Start Date'),'Start Date')

                    var _min = MINX(allselected('End Date'), 'End Date'[Date])

                   return

IF (MIN(Reservations[StartDate]) <= _min && MAX(Reservations[EndDate]) >= _max, 1 ,0)

Did I implemented your formula wrong then?

Thanks in advance,

Kerre