Forum Discussion

ShamR9T's avatar
ShamR9T
Frequent Visitor
3 years ago
Solved

Calculate Monthly Occupancy based on Checkin Checkout dates

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 tha...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

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's avatar
ShamR9T
Frequent Visitor
3 years ago

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.