Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Olabenji
Regular Visitor

Lenght of Stay

Room_IDGuest_IDDate_fromDate_to
109741/1/20166/1/2016
217851/1/20152/1/2017
210161/1/20166/1/2016
116531/1/20175/1/2017
2091291/1/20163/1/2018
214822/1/20163/1/2016
215962/1/20173/1/2018
204132/1/20167/1/2017
108183/1/20146/1/2016
1151015/1/20168/1/2018
207125/1/20157/1/2016
104236/1/20167/1/2018
4031387/1/201612/1/2016
111157/1/201611/1/2017
4041317/1/201712/1/2018
106957/1/201412/1/2016
304958/1/20169/1/2018
302178/1/201613/01/2016
2121099/1/201612/1/2016
30249/1/201514/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 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.