Calculate average room price between check in check out

[MaicLioulias]

Hi,

Depending on the the date range a guest is staying, I want to calculate the revenues per month.

For example, I want the average daily revenues of the reservations below to be split depending on the month they belong. 

More precisely, in the first row:

- (25/3 - 31/3) * 92.25

&

- 1/4 * 92.25 (checked out date is not counted as stay in)   

Checked In

25/03/23

02/04/23

92,25

Checked In	26/03/23	02/04/23	94,80
Checked In	26/03/23	02/04/23	123,88

I don't know if my information are clear but thank you in advance for any help.

[olgad]

@MaicLioulias Have my answer solved your problem? If so, can you please accept my answer as Solution to help other members find it faster? Thank you very much for your kind cooperation!

[olgad]

Hi,  not sure how you want to slice it and dice it, but here are some ideas. 

Datediff = DATEDIFF( Hotel[Checked In],Hotel[Check out],MONTH)

First half of Stay = If(Hotel[Datediff]>0, Datediff( Hotel[Checked In],EOMONTH(Hotel[Checked In],0),DAY)+1, DATEDIFF(Hotel[Checked In],Hotel[Check out],DAY))

2nd half of stay = If(Hotel[Datediff]>0, DATEDIFF(EOMONTH(Hotel[Checked In],0)+1, Hotel[Check out],DAY),Hotel[Datediff])

This shall give you the calcs for the day difference that can be now multiplied with the respective rates. 

Another way is to transform your data in power query.
Add previous day to a check out day

Change the data type to date

Now you can create a calendar table hook it up with dates of stay and that is it, have your revenue calculated easily. 

Please refer to this article to learn more: 

https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?tr...