topic Re: Timeline help: hotel room booking system in Desktop

Timeline help: hotel room booking system

karimk — Wed, 04 Oct 2017 16:25:19 GMT

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.

Re: Timeline help: hotel room booking system

MFelix — Wed, 04 Oct 2017 17:03:43 GMT

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

Re: Timeline help: hotel room booking system

karimk — Fri, 06 Oct 2017 22:07:22 GMT

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!

Re: Timeline help: hotel room booking system

TomMartens — Fri, 06 Oct 2017 22:25:30 GMT

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

Re: Timeline help: hotel room booking system

MFelix — Sat, 07 Oct 2017 15:08:05 GMT

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

Re: Timeline help: hotel room booking system

karimk — Mon, 09 Oct 2017 20:17:31 GMT

Hey. Try the Escel file in the link below.

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

Thank you very much!

Re: Timeline help: hotel room booking system

MFelix — Thu, 12 Oct 2017 22:27:18 GMT

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

Re: Timeline help: hotel room booking system

karimk — Fri, 13 Oct 2017 14:56:31 GMT

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.

Re: Timeline help: hotel room booking system

karimk — Fri, 13 Oct 2017 15:03:35 GMT

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

Re: Timeline help: hotel room booking system

MFelix — Fri, 13 Oct 2017 16:00:09 GMT

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

Re: Timeline help: hotel room booking system

karimk — Fri, 13 Oct 2017 17:46:12 GMT

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!

Re: Timeline help: hotel room booking system

MFelix — Fri, 13 Oct 2017 19:03:00 GMT

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

Re: Timeline help: hotel room booking system

MFelix — Sat, 14 Oct 2017 15:20:33 GMT

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

Re: Timeline help: hotel room booking system

karimk — Wed, 18 Oct 2017 13:46:33 GMT

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

Re: Timeline help: hotel room booking system

MFelix — Wed, 18 Oct 2017 16:37:55 GMT

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

Re: Timeline help: hotel room booking system

karimk — Thu, 19 Oct 2017 17:06:15 GMT

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.

Re: Timeline help: hotel room booking system

MFelix — Fri, 20 Oct 2017 09:41:42 GMT

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

Re: Timeline help: hotel room booking system

karimk — Tue, 24 Oct 2017 17:51:43 GMT

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?

Re: Timeline help: hotel room booking system

MFelix — Wed, 25 Oct 2017 09:17:44 GMT

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

Re: Timeline help: hotel room booking system

karimk — Tue, 31 Oct 2017 17:56:01 GMT

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.