Forum Discussion
Group the column based on two date columns
Hello teachers.
I have some hotel data for future reservations.
All the reservations have rate categories, rate, check-in date, check-out date, and other fields.
I wanted to compare past reservations vs future reservations using rate categories. That way reports can tell me who is giving more business.
Future reservations:
| room_no | room_type | source | arrival_date | departure_date | persons | rooms | nights | rate | rate_cat | made_on | by_user |
| 211 | XLON | Booking.com | 10/16/2022 | 11/8/2022 | 1 | 1 | 23 | 118.85 | IDME2 | 9/22/2022 | john |
| 201 | XSTN | Expedia | 10/22/2022 | 11/4/2022 | 1 | 1 | 13 | 139.92 | IDME2 | 10/20/2022 | jane |
| 231 | KNGN | 10/22/2022 | 11/4/2022 | 1 | 1 | 13 | 129.63 | ILW4L | 10/20/2022 | john | |
| TQNN | Microsoft | 10/24/2022 | 11/7/2022 | 1 | 1 | 14 | 99.2 | IKME3 | 9/11/2022 | john | |
| TQNN | Apple | 10/25/2022 | 10/31/2022 | 2 | 1 | 6 | 122.55 | IKME3 | 10/21/2022 | john | |
| KNGN | 10/25/2022 | 10/31/2022 | 1 | 1 | 6 | 117.8 | IKME3 | 10/21/2022 | jane | ||
| KNGN | 10/25/2022 | 11/3/2022 | 1 | 1 | 9 | 98 | IMGOV | 10/24/2022 | jane | ||
| KNGN | 10/27/2022 | 11/1/2022 | 1 | 1 | 5 | 110 | IGNSF | 10/26/2022 | jane | ||
| KNGN | Expedia | 10/27/2022 | 11/1/2022 | 1 | 1 | 5 | 112.5 | IMMLR | 10/27/2022 | jane | |
| KNGN | Priceline | 10/27/2022 | 12/5/2022 | 2 | 1 | 39 | 87.88 | IGNSF | 10/23/2022 | jane |
Is there a way to show each rate category with their month-year, total nights, and average rate as below?
| rate_cat | month_year | nights | avg_rate |
| IDME2 | Oct-22 | 26 | 129.39 |
| IDME2 | Nov-22 | 10 | 129.39 |
| IGNSF | Oct-22 | 10 | 98.94 |
| IGNSF | Nov-22 | 30 | 98.94 |
| IGNSF | Dec-22 | 4 | 98.94 |
| IKME3 | Oct-22 | 20 | 113.18 |
| IKME3 | Nov-22 | 6 | 113.18 |
| ILW4L | Oct-22 | 10 | 129.63 |
| ILW4L | Nov-22 | 3 | 129.63 |
| IMGOV | Oct-22 | 7 | 98 |
| IMGOV | Nov-22 | 2 | 98 |
| IMMLR | Oct-22 | 5 | 112.5 |
I am new to Power Bi. And I am not sure how can you obtain this kind of result from the data above in PB.
Thank you all for your help.
HI Devtr,
It sounds like a common multiple date fields analysis requirement, you can take a look at Greg's blog 'start date', 'end date' parts if it helps:
Regards,
Xiaoxin Sheng
2 Replies
HI Devtr,
It sounds like a common multiple date fields analysis requirement, you can take a look at Greg's blog 'start date', 'end date' parts if it helps:
Regards,
Xiaoxin Sheng
I made the row data as monthly. I added few additional columns in power query. And resolved the issue. Thanks though.
