Calculate Monthly Occupancy based on Checkin Checkout dates

[ShamR9T]

I have a problem I want to solve. I have a main table which is the reservations and has details of the guest. it has details like nationality, checkin date, biographical date etc. this also means that there are multiple guests that can checkin and checkout on the same date. here is some sample data. I want to create a dynamic measure by connecting this to a dates table where you can calculate the daily occupancy rate based on their checkin and checkout date and then be able to calculate the total room revenue for the day and which nationality they are from.

I can do it for daily using a simple measure where the the date from date table is greater than checkin date and smaller than checkout. but I cant aggregate it to a monthly or quarterly basis where I want to calculate all the nights the room was occupied in that month.

For an example someone who checks in on 30th December and checks out on 2nd January has 2 nights (30th and 31st December) in December and 1 night (January 1st) in January. I hope my question is clear and you guys are able to help.

Thanks in Advance

here is a sample dataset

Checkin	Checkout	Duration	Nationality	Room Rate	Property
12/1/2022	12/11/2022	10.00	British	120	Italian
1/1/2023	2/15/2023	45.00	American	220	Italian
12/28/2022	1/21/2023	24.00	British	145	Ibiza
5/1/2023	5/11/2023	10.00	British	320	Ibiza
9/11/2022	10/1/2022	20.00	Australian	111	Ibiza
5/1/2023	5/9/2023	8.00	American	220	Italian
5/1/2023	5/21/2023	20.00	Australian	111	Ibiza

This is the outcome I am hoping to achieve (PS. I havent done the calculations thoroughly so might be some errors but I hope you understand) and I want this to be filtered by nationality or property etc. 

Month	Year	Room Nights
December	2022	44
January	2023	60
February	2023	15

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Room nights measure: =
VAR _newtable =
    GENERATE (
        Data,
        FILTER (
            DISTINCT ( 'Calendar'[Date] ),
            'Calendar'[Date] >= Data[Checkin]
                && 'Calendar'[Date] <= Data[Checkout]
        )
    )
RETURN
    COUNTROWS ( _newtable )

[ShamR9T]

Sorry. Just to also add. I dont think this solution is accounting for guests that checked in prior to the beginning of the period either. so for example someone who checked in on December 31st and checked out on February 2nd isnt accounted for in January. 

[ShamR9T]

Thats actually a very interesting solution. Thank you so much. yes. it has definitely helped with the problem of getting the data on monthly basis. And I think you understood my predicament quite well. 

Just One more question. If I am to replicate this for quarterly figures, or any other period, should I add a new column specifying the quarter and then continue like that as well?

I did try this solution as well from a different post. But cant seem to replicate the same logic here. DAT being the main data table and Sheet 2 being the date table. But it keeps outputting blank values. 

Thanks for your help.