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.

  • ShamR9T's avatar
    ShamR9T
    Frequent Visitor
    3 years ago

    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.