Forum Discussion
Calendar
I have a table about hotel réservations with customer id, start and end dates (1 day or 2, no limit), room number
Id, CustomerId, RoomNumber, StartDate, EndDate
1,1,1,1/1/2019,20/1/2019
2,2,2,1/1/2019,2/1/2019
3,3,2,3/1/2019,5/1/2019
4,4,2,7/1/2019,20/1/2019
I would like know, for a specific day, how many customers on each rooms
For example on 6/1/2019 only 1 room occupied, 2 for other days between 1/1/2019 ans 20/1/2019
Do you have any Idea how to manage that please ?
Regards
Régis
Hey,
maybe the phrase "after expanding ..." from my 1st post has been too short :-)
Expand the List to new rows in your new column by hitting the "double arrow" icon.
After this you will be able to convert the data type of the column into the data type date
Regards,
Tom
7 Replies
Hey,
here you will find a little pbix file that contains some sample data ...
The data looks like this:Basically it creates a list of dates using the PowerQuery function List.Dates inside the Custom Column formula:
List.Dates( [Date Start] , Number.From([Date End])-Number.From([Date Start])+1 , #duration(1,0,0,0) )
After expanding the list to rows the table will look like this:
This allows to create a matrix visual with the Room Number on columns and the new date column on rows like so:
Please be aware that you have to prepare for missing data in the Date End column, maybe assuming a stay that projects 30day into the future like so "if [Date End] is null then ..."
Hopefully this is what you are looking for.
Regards,
Tom