Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Condo/ Property Availability

I'm apologizing in advance as I'm well aware that this topic has been covered many times before. Though in all honesty, not exactly in the way that I am needing. It seems that when figuring Occupancy...
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Anonymous,

     

    You can refer to below steps if it suitable for your requirement.

     

    Steps:
    1. Create a expand table with detail date from date range of each record.

     

    Expand Table = 
VAR _calendar =
    CALENDAR ( FIRSTDATE( Reservation[Check In] ), LASTDATE( Reservation[Check out] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Reservation, _calendar ),
            [Date] >= [Check In]
                && [Date] <= [Check out]
        ),
        "Reservation ID", Reservation[Reservation ID],
        "Detail Date", [Date]
    )

     

    2. Link original table by 'Reservation ID' column.

     

    3. Write measure to check dynamic status of selection date range.

    Available =
IF (
    LASTDATE ( ALLSELECTED ( 'Expand Table'[Detail Date] ) )
        IN CALCULATETABLE (
            VALUES ( 'Expand Table'[Detail Date] ),
            ALLSELECTED ( Reservation[Room Id] )
        ),
    "N",
    "Y"
)

    4. Create visuals.

     

    Notice: I attach pbix file below.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI Anonymous,

 

You can refer to below steps if it suitable for your requirement.

 

Steps:
1. Create a expand table with detail date from date range of each record.

 

Expand Table = 
VAR _calendar =
    CALENDAR ( FIRSTDATE( Reservation[Check In] ), LASTDATE( Reservation[Check out] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Reservation, _calendar ),
            [Date] >= [Check In]
                && [Date] <= [Check out]
        ),
        "Reservation ID", Reservation[Reservation ID],
        "Detail Date", [Date]
    )

 

2. Link original table by 'Reservation ID' column.

 

3. Write measure to check dynamic status of selection date range.

Available =
IF (
    LASTDATE ( ALLSELECTED ( 'Expand Table'[Detail Date] ) )
        IN CALCULATETABLE (
            VALUES ( 'Expand Table'[Detail Date] ),
            ALLSELECTED ( Reservation[Room Id] )
        ),
    "N",
    "Y"
)

4. Create visuals.

 

Notice: I attach pbix file below.

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous This looks amazing. I'll take a look now to see how his works. I'll let you know shortly!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous Thank you again. This appears as though it will work for my needs. I do have a question to pose. As my "dummy" reports are very small, creating a "detail date" for each day of a reservation works smoothly. However, my actual report contains nearly 40,000 reservations over the course of 4 years and averages almost 120 new (avg. 6 nights per reservation) reservations daily. Creating a "detail date" for that many reservations may seem very daunting of a task. As we speak I've been waiting 20 minutes with a status of "Working on it" for the Expand Table to complete.

       

      Do you think given the magnitude of my data this is still the best approach? Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI Anonymous,

         

        Unfortunately,, I haven't found other method to direct operate/calculate on date range which defined by two columns.

        Maybe you can try to calculate these in measure formula and write tag measure to filter records on visual level filter.

         

        It should be a way but seems more complex.(actually power bi not support operate date range who not have real records)

         

        Regards,

        Xiaoxin Sheng