Lenght of Stay

[Olabenji]

Room_ID	Guest_ID	Date_from	Date_to
109	74	1/1/2016	6/1/2016
217	85	1/1/2015	2/1/2017
210	16	1/1/2016	6/1/2016
116	53	1/1/2017	5/1/2017
209	129	1/1/2016	3/1/2018
214	82	2/1/2016	3/1/2016
215	96	2/1/2017	3/1/2018
204	13	2/1/2016	7/1/2017
108	18	3/1/2014	6/1/2016
115	101	5/1/2016	8/1/2018
207	12	5/1/2015	7/1/2016
104	23	6/1/2016	7/1/2018
403	138	7/1/2016	12/1/2016
111	15	7/1/2016	11/1/2017
404	131	7/1/2017	12/1/2018
106	95	7/1/2014	12/1/2016
304	95	8/1/2016	9/1/2018
302	17	8/1/2016	13/01/2016
212	109	9/1/2016	12/1/2016
302	4	9/1/2015	14/01/2017

Hi guys I have a table looking as above. I need to do a report that shows occupancy level. that is lenght of stay of each guest for each period.

For example : Guest 85 in room 217.
                     I want to know how long he/she stayed for years 2015, 2016 and 2017 seperately. 

                     That is Year 2015 : 365 days

                                Year  2016: 365 days

                      and    year 2017: 32 days

Please all help is well appreciated. I have been stuck on this for some days now. 

Thanks 

[OwenAuger · Accepted solution]

Hi @Olabenji

(This PBIX file contains an example of the below suggestions.)

I would suggest a couple of general options. In both cases, you should have a Calendar table to allow filtering of dates/years:

1. Leave your data table as is (I have called it Occupancy), and create a measure like one of these:
    

   Length of Stay (Occupancy) = 
   SUMX (
       Occupancy,
       CALCULATE (
           COUNTROWS ( 'Calendar' ),
           KEEPFILTERS (
               DATESBETWEEN ( 'Calendar'[Date], Occupancy[Date_from], Occupancy[Date_to] )
           )
       )
   )
   
   Length of Stay (Occupancy) v2 = 
   SUMX (
       ADDCOLUMNS (
           SUMMARIZE ( Occupancy, Occupancy[Date_from], Occupancy[Date_to] ),
           "Rows", CALCULATE ( COUNTROWS ( Occupancy ) )
       ),
       CALCULATE (
           COUNTROWS ( 'Calendar' ),
           KEEPFILTERS (
               DATESBETWEEN ( 'Calendar'[Date], Occupancy[Date_from], Occupancy[Date_to] )
           )
       )
           * [Rows]
   )

2. Reshape your data table so that you have a single Date column which covers the range Date_from to Date_to (I have called this Occupancy Expanded), and create a much simpler measure:
   

   Length of Stay (Occupancy Expanded ) = 
   COUNTROWS ( 'Occupany Expanded' )

The above measures all sum the number of guest/days.

I also created lookup tables for Guest & Room

Anyway, those are the basic structures I would recommend. You could include them both in the same model and use them for different purposes.

Regards,

Owen