Calculating number of nights stayed at Hotel

[Anonymous]

Hi!

I'm working on a hotel report but have issues when I'm calculating the number of nights stayed by any customer. In my dataset each booking get a reference number with information about the date of arrival and the number of night that customer is going to stay. So if a customer comes in 2018-01-31 and stays for 5 nights I can not come up with a good way to calculate this. When I try to do the calculation that booking would shown as 5 nights spent in january, even though only one night was spent in January and the rest was spent in February.

I've attached a example dataset here, with a small explanation of how I would like the dataset to look, but I'm open to all suggestions if there is a better way to solve this problem.

Dataset Hotel - Number of Nights Stayed

Very thankful help in this issue!

/Patrick

[Zubair_Muhammad]

@Anonymous

You can use this Calculated Table

From Modelling Tab>>New Table

Calculated Table =
VAR temp =
    ADDCOLUMNS (
        GENERATE ( Table1, GENERATESERIES ( 0, [Number of nights] - 1 ) ),
        "My_Dates", [Date] + [Value]
    )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( temp, "Booking Number", [Booking number], "Date", [My_Dates] ),
        "Number of Nights", 1
    )

[Anonymous]

Hi again Zubair!

The dataset I provided was a simplified model that I hoped that I could use on my more complicated dataset, but I could not get it to work. Here I've provided another dataset that more closely resembles the actual dataset that I want to use your solution for.

The column that states the number of nights is the one called "AntNaetter", booking number is "ReservationsNr" and the Date of arrival column is "Ankomst".

Dataset Excel File

If you would be able to provide me with a solution for this dataset as well you would have my eternal gratitude!! 

[Zubair_Muhammad]

@Anonymous

What is your desired output?

You can give me few rows of data with expected output

[Zubair_Muhammad]

@Anonymous

Please see attached file as well

[Anonymous]

Thank you I'll check this out ASAP!