Daniel Kuenstler
New Member
- Joined
- Apr 10, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
- MacOS
- Web
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!!!!
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!!!!