Challenge!!

Daniel Kuenstler · Apr 10, 2020

Hi,
I'm confronted with a quite challenging task, well at least for me. I already asked a lot of colleagues, but nobody was able to find the solution. Maybe someone can help me out with this here.
Here is my task: I have to calculate the real occupancy (in %) for tourist accommodation. The database I have provides check-in and a check out date. So I can easily calculate how many days/nights each booking has stayed. Further, I created a column where I assigned to each month, the number of days in that month. (30, 31 or 28 in February). Now to calculate the days the establishment was occupied, I just need to divide the sum of days booked in that month (getting them from each booking) by the days in that specific month. Let´s say: 10 nights sold in March....10/31=0,32 or 32%. Easy right? Now my assignment is to calculate the "real " occupancy. That means only the days in that month!!! In other words: if the booking of 10 nights starts on the 25th of march, then we should only calculate 5 days for March and the other 5 days should "pass over" to April. But how can I do this? How can I say to Exel (or Power BI, because I tried it as well with DAX) to count only the days that will fall into this month AND keep the "rest" of the stay for the next month? I´m stuck with this problem for several weeks now and hope that someone might pick up that challenge. (I don´t mind if you provide a solution with DAX or for Exel since I´ve tried to take the project as well to Power BI). I leave you with a link to a sample file. Feel free to experiment and thank you in advance for any help provided. sample file here!!!!

steve the fish · Apr 10, 2020

This will be all about how you have the workbook setup. Think id create a matrix of days of the year down column A then the property names along Row 1. This can then be populated by another sheet which shows bookings and dates. Then the matrix in turn can populate a monthly sheet/s for each property. There will be a lot of ways to get this done that said.

Daniel Kuenstler · Apr 10, 2020

Hi Steve, thank you for your reply!! Not sure if I understand well what you mean exactly with creating the new matrix....probably the easy part is to calculate the days in the same month...But how can I get the remaining days to the next month? Feel free to add any changes or notes to the sample!!!! Appreciate your help!

jasonb75 · Apr 10, 2020

Daniel Kuenstler said:
if the booking of 10 nights starts on the 25th of march, then we should only calculate 5 days for March and the other 5 days should "pass over" to April.

Are you excluding weekends then? I would say that it should be 7 days in March (25,26,27,28,29,30,31) and 3 in April

The solution also depends how you want the results to be shown, as some scenarios can return misleading results if handled incorrectly. Take a longer date range, for example: 16th April to 16th June. Should the result be 200%? (50% of April +100% of May + 50% of June)?

This formula, array confirmed in L2 gives results, but they may or may not be correct.

=SUM(IFERROR(FREQUENCY(ROW(INDIRECT(E2&":"&(F2-1))),EOMONTH(ROW(INDIRECT(E2&":"&(F2-1))),0))/DAY(EOMONTH(ROW(INDIRECT(E2&":"&(F2-1))),0)),0))

steve the fish · Apr 10, 2020

Take these two dates:

28/02/2020
05/03/2020

One in A1, other in B1. To calculate the number of days inclusive is simply:

=B1-A1+1

But provided you dont have stays that could last over a month then for February days its:

=EOMONTH(A1,0)-A1+1

and March

=B1-MAX(EOMONTH(B1,-1)+1,A1)+1

Snakehips · Apr 10, 2020

Maybe something like below to create helper columns that split the month (assuming only two). Then the month count would be sum for the month of both columns.
I have had to switch the date format for it to function here.

Book1