Percentage of rooms checked out in a month that were booked in previous months

[TwinkleMittal]

Hi, 

I have hotel data : Reservation Date, CheckOut Date, Room, etc. 

Suppose 500 rooms were booked/reserved in a month (Jan), out of which 60 were checked out in the same month(Jan), 50 in next month (feb), 40 in march and so on. 

I want to get the %  of rooms checked out in jan, feb, march, etc that were booked in Jan itself using DAX expressions. 

Can anyone please help me? 

[Fowmy]

@TwinkleMittal 

Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[TwinkleMittal]

@Fowmy 

Below is the sample data. 

ResDate	CheckOutDate	RoomNights
01-03-22	10-04-22	4
01-03-22	20-04-22	3
01-03-22	06-05-22	2
01-03-22	10-05-22	1
01-03-22	23-05-22	1
01-03-22	19-06-22	2

As you can see in the table above, the bookings (ResDate) were made in march but is going to be checked out (CheckOut Date) in April, may and june. I want to see the percentage distribution of all the bookings made in march over the future months. 

In this case, 13 bookings were made in March 2022, out of which 53.8% (3+4 = 7) is going to be checked out in April,  30.76% in may and 15.38% in June. I want to get this percentage value. It can be a measure or a column. 

Thanks, 

Twinkle 

[v-stephen-msft · Accepted solution]

Hi @TwinkleMittal ,

Here's my solution.

1.Create a Month column.

Month = FORMAT([CheckOutDate],"MMM")

2.Create a measure.

Percentage = 
VAR _TOTAL =
    CALCULATE ( SUM ( 'Table'[RoomNights] ), ALL ( 'Table' ) )
VAR _SUB =
    CALCULATE ( SUM ( 'Table'[RoomNights] ), ALLEXCEPT ( 'Table', 'Table'[Month] ) )
RETURN
    DIVIDE ( _SUB, _TOTAL )

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.