Timeline help: hotel room booking system

[karimk]

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.

[MFelix · Accepted solution]

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

[MFelix]

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

[karimk]

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!

[MFelix]

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

[karimk]

Hey. Try the Escel file in the link below.

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

Thank you very much!

[MFelix]

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:

Hope this is the result you need.

Regards,

MFelix

[karimk]

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.

[MFelix · Accepted solution]

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

[karimk]

You solved it! Thank you very much!!!

The only thing that I´m missing now, but I think it´s a matter of Power BI functionality, is to have more coloring options with conditional formatting.

Example:

IF the Production Name is anything between 1800 and 2299 -> Red. If it´s between 1000 and 1799 -> Gray. If it´s between 3000 and 4000 -> purple.

Well. Thanks! 

[MFelix]

Hi @karimk,

don'ts know if this solve your issue but you can always link a unichar to your code and then use it in your measure and you will have a production number and a symbol connected to your data:

Occupation with symbol: =
SWITCH (
    TRUE (),
    occupation[Ocupation_Dates] = BLANK (), BLANK (),
    [Ocupation_Dates] >= 1
        && [Ocupation_Dates] <= 2, occupation[Ocupation_Dates] & UNICHAR ( 9971 ),
    [Ocupation_Dates] >= 3
        && [Ocupation_Dates] <= 4, occupation[Ocupation_Dates] & UNICHAR ( 9977 ),
    occupation[Ocupation_Dates] & UNICHAR ( 9978 )
)

Again for this I used the last number in the Production column to make it easier.

For getting the UNICODE characters go to this link and use the HTML code for each character you need.

Just trying to think outside the box not sur if this is what you want.

Regards,

MFelix

[karimk]

Would this unicode approach work if the production was a text, instead of a number?

[MFelix]

Sure I just placed a number to facilitate you need to adjust the formula to your request.

Do you have a sample of the values you wish to include?

I can do a quick change to the formula.

Regards,

MFelix

[karimk]

Hi,

The Dropbox link below has an Excel file with a sample database. 

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

Basically I would like to put a colored unicode next to each "PRODUCTION" name on the timeline, based on it´s "GÊNERO".

The GÊNERO-color relation is on the "UNICODE REF" sheet.

The unicode image would have to be as neutral as possible (a rectangule, for example).

Is this possible?

Thank you so much for still helping.

[MFelix]

Hi @karimk

Try this measure, again you can choose the unicode character on link.

Occupation with symbol: =
SWITCH (
    TRUE (),
    occupation[Ocupation_Dates] = BLANK (), BLANK (),
    VALUES ( occupation[GÊNERO] ) = "Dramaturgia Diária", occupation[Ocupation_Dates] & UNICHAR ( 9650 ),
    VALUES ( occupation[GÊNERO] ) = "Dramaturgia Semanal", occupation[Ocupation_Dates] & UNICHAR ( 9724 ),
    VALUES ( occupation[GÊNERO] ) = "Escritorial", occupation[Ocupation_Dates] & UNICHAR ( 9670 ),
    VALUES ( occupation[GÊNERO] ) = "Variedades Diária", occupation[Ocupation_Dates] & UNICHAR ( 9677 ),
    VALUES ( occupation[GÊNERO] ) = "Variedades Noite", occupation[Ocupation_Dates] & UNICHAR ( 9608 ),
    occupation[Ocupation_Dates]
)

 Regards,

MFelix

[karimk]

Hi. Thanks a lot.

I can´t get colored unicodes. It seems so because I have Windows 7. I´m looking into other options.

Is it possible to do the same kind of measure you did, but instead of unicode, an image from a URL? 

[MFelix · Accepted solution]

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

[karimk]

And now an evolution of the solution you gave me:

Is it possible to also give blank spaces of time (with no production value) a "value"? My goal is to filter a specific date and have it show me all the blank spaces, so I can be sure where I can alocate a production. 

[MFelix]

Not sure if this is what you need but if you change this part of the formula you will get a "value" instead of blank value:

Occupation with symbol: =
SWITCH (
    TRUE (),
    occupation[Ocupation_Dates] = BLANK (), "VACANT",
    VALUES ( occupation[GÊNERO] ) = "Dramaturgia Diária", occupation[Ocupation_Dates] & UNICHAR ( 9650 ),
    VALUES ( occupation[GÊNERO] ) = "Dramaturgia Semanal", occupation[Ocupation_Dates] & UNICHAR ( 9724 ),
    VALUES ( occupation[GÊNERO] ) = "Escritorial", occupation[Ocupation_Dates] & UNICHAR ( 9670 ),
    VALUES ( occupation[GÊNERO] ) = "Variedades Diária", occupation[Ocupation_Dates] & UNICHAR ( 9677 ),
    VALUES ( occupation[GÊNERO] ) = "Variedades Noite", occupation[Ocupation_Dates] & UNICHAR ( 9608 ),
    occupation[Ocupation_Dates]
)

Regards,

MFelix

[MFelix]

Hi @karimk,

The condittional option only.works.with numbers not text so uf you keep the PRODUCTION1 it will not return condittional option also it.don't work with 3 options.

Regards
MFelix

[karimk]

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

[TomMartens]

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