Ocupation table

[Anonymous]

Hi!

I have a table with reservations. I have check in date and number of nights.

I want to create a visual with the ocupation each day in the hotel, for example a bar chart. To get this i want to create a calendar table with the ocupated rooms each day.

So for example If the check in date is on 10 of april and the number of nights are 2 there will be 1 occupated room on 10/04 and 11/04.

The table format would be      DATE          Rooms Occupated

                                                     .                      .

                                                 10/04/2019       1

                                                 11/04/2019       1

                                                 12/04/2019       0

How can I create these table?

Thank you in advance

[PattemManohar]

@Anonymous  Just to confirm - The above table is your expected output based on the input table (which contains checkin data and no. of nights booked). Could you please post sample test input data and assuming the above table is your expected output (Date and Rooms Occupated)

[Anonymous]

@PattemManohar  Here you have what you asked for.  The output 2 is the desired but i conform with output 1 too

[PattemManohar · Accepted solution]

@Anonymous  Please try this as a New Table to achieve your desired output i.e. Output 2 as mentioned above.

Test304Out = 
VAR _Date = SELECTCOLUMNS(GENERATE(CALENDAR(MIN(Test304RowSplit[CheckIn]),MAX(Test304RowSplit[CheckIn])),Test304RowSplit),"Date",[Date],"ID",[ID],"CheckOut",Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"ExcludeFlag",IF([Date]>=Test304RowSplit[CheckIn] && [Date]<=Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"N","Y"))
RETURN SELECTCOLUMNS(FILTER(_Date,[ExcludeFlag]="N"),"Date",[Date],"ID",[ID])