cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karimk Regular Visitor
Regular Visitor

Timeline help: hotel room booking system

Hi!

I have a tough one. Is it possible in any way to make a timeline-like chart to show how my building are occupied during the year? Considerations:

-One axis shows months and weeks

-Another axis shows my buildings, by floor

-The timeline "filling" would be the teams occupying each floor

-I can have blank periods where no one would be using that space

-Preferably the name of the occupant would show on the bar filling the timeline, but we could have a sepparate table for that.

An example done in Excel:

example genoma.PNG

 

 

 

 

Any ideas?

 

Thank you very much.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

Ansewring on question 1 and on the problem you have, I have made an error on the formula  were  we put Calendar[Week] should be [Week_Year] below is the corrected measure I have made the copy before finalizing my formula, sorry

 

Ocupation_Dates = 
VAR Week_number =
    MAX ( 'Calendar'[Week_Year] )
RETURN
    CALCULATE (
        MIN ( occupation[PRODUCTION] ),
        occupation[Start_Week] <= Week_number,
        occupation[End_week] >= Week_number
    )

 

So this solves your issue of blank values in matrix and respond to your first question the Week_Year column is how I define the start and stop on the matrix values

 

Thougt process:

The measure are based on context so when you add the columns (dates) and the Rows (buildings) all the calculations will be made with the "crossing" of the information of rows and columns.

 

The variable Week number makes a context validation within the matrix and returns that column week number by year

On the calculate I choose the Production value at the Year/month/week choosen and at the building. So based on that what the formula does is to compare the Week Number on the Column and check if the Start Week is higher than that and the End week is lower so based on that returns the values of the Production.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

You can do this with image URL however the part of the name will not be available URL images can only be displayed with URL and not composed formulas.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

20 REPLIES 20
Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

You can try and use the matrix visual, don't have a complete dataset example but the final result would look something like this:

 

grid.png

If you want more explanations on how to achieve this if you give me a sample data I can try and make you a small guide.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




karimk Regular Visitor
Regular Visitor

Re: Timeline help: hotel room booking system

Thank you very much for the response. That pic you uploaded seems like the right direction. Would it be possible to color specific cells in the matrix? Like: AA is red, BB is blue, etc.

 

I´m uploading a pic of a simple spreadsheet with data example that should become the pic you showed. I don´t know how to upload an Excel file here.

 

Thanks!

Super User
Super User

Re: Timeline help: hotel room booking system

Hey, upload the excel file to onedrive or dropbox and just share the link. This will save the people a lot of time and also avoid typos

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

I have made my first response based on that but no sure how you have your data, can you make a copy paste of part of the table to a post or send a onedrive / google drive / wetranfer link to have a better understanding of your data.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




karimk Regular Visitor
Regular Visitor

Re: Timeline help: hotel room booking system

 

Hey. Try the Escel file in the link below.

https://www.dropbox.com/sh/czvw71jmrn7l2so/AADKdLqbpLJxHPtWtsqv1-gZa?dl=0

 

Thank you very much!

Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

Sorry for the late response, please use the following schematic:

 

Create a calendar table (check this link for a more complex table) I just add a new table with the following sintax:

Calendar = CALENDAR("01/01/2017","31/12/2018")

On the calendar date add the following columns:

Week = WEEKNUM('Calendar'[Date])

Week_year = YEAR('Calendar'[Date])&FORMAT(WEEKNUM('Calendar'[Date]),"00")

On your occupation table create the following columns:

Start_Week = YEAR(occupation[START DATE])&FORMAT(WEEKNUM(occupation[START DATE]),"00")

End_week = YEAR(occupation[END DATE])&FORMAT(WEEKNUM(occupation[END DATE]),"00")

This will give you the star and end week for each line in your table in order to fill out your matrix.

 

Also add this measure:

Ocupation_Dates = 
VAR Week_number =
    MAX ( 'Calendar'[Week] )
RETURN
    CALCULATE (
        MIN ( occupation[PRODUCTION] ),
        occupation[Start_Week] <= Week_number,
        occupation[End_week] >= Week_number
    )

 

Now that you have set up your table go and create a matrix visual (for simplification purposes I used only the last digit of the production so the matrix could be visible in full):

 

Matrix visual:

Rows:

  • Occupation Table - Building
  • Occupation Table - Floor

Columns

  • Calendar Table - Date (Hierarchy - Year)
  • Calendar Table - Week

Values

  • Occupation Table - Occupations_Dates (measure)

Option on the matrix:

Row headers:

  • Stepped Layout - Off
  • Drill down on row and columns all the way.

In the image below you can see the final table and also how I create the table with the setup above:

 

occupation.gif

 

Hope this is the result you need.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




karimk Regular Visitor
Regular Visitor

Re: Timeline help: hotel room booking system

MFelix, this looks brilliant. Thank you so much for your help!!

Unfortunately I haven´t been able to reproduce it yet. My problem seems to be with the measure, especially with the "<= Week_number" and "=> Week_number".

What happens is that the matrix displays blank values.

Below is a pic of how I wrote the measure. Is it right?

matrix measure.PNG

If I don´t write "<= Week_number" and "=> Week_number", the matrix will display values, but, obviously, they are incorrect.

In the pic below the top matrix has a measure with no "<= Week_number" syntax. In the bottom matrix the measure does have the "<= Week_number" syntax.

matrix blank.PNG

 

 

Below are the Dropbox links for the PowerBI file and the updated database. I will use IDs instead of text for the production names. This will help me use conditional formatting. 

https://www.dropbox.com/s/jhut185fvc4dn42/TIMELINE%20MFELIX.pbix?dl=0

 

https://www.dropbox.com/s/sxw9my19rkahx6u/TIMELINE%20TEST.xlsx?dl=0

 

Besides all this, I have some questions, that if you have the time to answer it would be amazing:

1) Where was the Week_year column used in all this?

2) What is the thought process behind the measure you created?

3) What should I do if I want to display at the same time Year, Month and weeks, instead of the current Year and Weeks format? 

 

Thank you so much. If I´m asking too much, don´t worry. I´ll try to solve this another way. You have already helped a lot.

karimk Regular Visitor
Regular Visitor

Re: Timeline help: hotel room booking system

I just want to say that I solved my question 3) from the bottom of my reply.

 

Super User
Super User

Re: Timeline help: hotel room booking system

Hi @karimk,

 

Ansewring on question 1 and on the problem you have, I have made an error on the formula  were  we put Calendar[Week] should be [Week_Year] below is the corrected measure I have made the copy before finalizing my formula, sorry

 

Ocupation_Dates = 
VAR Week_number =
    MAX ( 'Calendar'[Week_Year] )
RETURN
    CALCULATE (
        MIN ( occupation[PRODUCTION] ),
        occupation[Start_Week] <= Week_number,
        occupation[End_week] >= Week_number
    )

 

So this solves your issue of blank values in matrix and respond to your first question the Week_Year column is how I define the start and stop on the matrix values

 

Thougt process:

The measure are based on context so when you add the columns (dates) and the Rows (buildings) all the calculations will be made with the "crossing" of the information of rows and columns.

 

The variable Week number makes a context validation within the matrix and returns that column week number by year

On the calculate I choose the Production value at the Year/month/week choosen and at the building. So based on that what the formula does is to compare the Week Number on the Column and check if the Start Week is higher than that and the End week is lower so based on that returns the values of the Production.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)