Group the column based on two date columns

[Devtr]

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	Google	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.

Download the excel file here 

Thank you all for your help.

[Devtr]

I made the row data as monthly. I added few additional columns in power query. And resolved the issue. Thanks though.

[Anonymous · Accepted solution]

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:

Before You Post, Read  

Regards,

Xiaoxin Sheng