Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
Proud to be a PBI Community Champion
@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)
Proud to be a PBI Community Champion
@PattemManohar Here you have what you asked for. The output 2 is the desired but i conform with output 1 too
@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])
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |