In this challenge it was required to calculate average hotel stay and count the number of hotel reservation IDs for all hotel reservation that were not cancelled and were at least one day in length. I decided to do it in 3 steps. First filtering out the non-cancelled reservation and adjusting the data type for date. I first filtered out the data for cancelled reservation status using a filter tool. Then I used a formula to transform the date into appropriate form and then used a datetime parse tp convert it into a date data type.
Figure 1: Filtering Non cancelled reservations
The second step was to calculate the length of stay which had at least 1 day. For this I used a formula tool and used this formula
DateTimeDiff({Arrival date New], [reservation status_date], “days”) Then to filter out data for at least a day length stay I used a filter
Figure 2: Calculating Length of stay
The third and final step was to count the reservation Ids and average hotel stay. For this step I used a summarize tool and grouped by arrival date month, average of length of stay and count distinct of reservation IDs.
Figure 3: Counting Reservation IDs