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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karimk
Helper III
Helper III

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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

20 REPLIES 20
MFelix
Super User
Super User

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



 

Hey. Try the Escel file in the link below.

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

 

Thank you very much!

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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! 

 

 

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.

 

unicode.png

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

 

 

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

 

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

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



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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