Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calendar

Hello
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
1 ACCEPTED SOLUTION

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

Hey,

 

here you will find a little pbix file that contains some sample data ...


The data looks like this:
image.png

 

Basically it creates a list of dates using the PowerQuery function List.Dates inside the Custom Column formula:

image.png

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:

image.png

 

 This allows to create a matrix visual with the Room Number on columns and the new date column on rows like so:

image.png

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thanks a lot Tom
I will try tomorrow and keep you un touch

Regis
Anonymous
Not applicable

Hello Tom

 

It works great to create list field, but it has been automaticaly convert in text...i cannot convert back to date and the link with a calendar table failed, because calendar stays in date format

 

Do you already have such an issue please ?

 

Regards

Hey,

 

using my pbix i have no problem to convert the the text field to data type date, neither using Power Query nor DAX.

 

I recommend do the conversion already in Power Query ... mark the new column and select 

Menu: Transform --> Date & Time column --> Date only

image.png

 

Maybe you will receive errors, but here you the possibility to investigate further.

 

Hopefully this gets you started.

 

Regards,
Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hello,

I really don't know why transform option unavailable

Look at the screen attached if you have any idea it will be a great help

 

Regards

 

Regis

 

Capture.PNG

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thnaks a lot Tom, you totally right abou expansion option

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.