cancel
Showing results for
Did you mean:
Highlighted
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:

Any ideas?

Thank you very much.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III

## 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

Miguel Felix

Proud to be a Datanaut!

Super User III

## 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

Miguel Felix

Proud to be a Datanaut!

20 REPLIES 20
Super User III

## 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:

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

Miguel Felix

Proud to be a Datanaut!

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 III

## 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
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Super User III

## 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

Miguel Felix

Proud to be a Datanaut!

Regular Visitor

## Re: Timeline help: hotel room booking system

Hey. Try the Escel file in the link below.

Thank you very much!

Super User III

## 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.

```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:

• 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:

Hope this is the result you need.

Regards,

MFelix

Regards

Miguel Felix

Proud to be a Datanaut!

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?

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.

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.

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 III

## 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

Miguel Felix

Proud to be a Datanaut!

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.