Help with How to Create a Timeline

[colettb]

Hi Community!

I am trying to make a graphic like below.  I have multiple studies with three main milestone dates (denoted by colored circles). I need to have a bar that shows the total time the project took (denoted by green, pink and blue lines) and then have some way to show where each of the 3 milestones occurred.  Any ideas or suggestions?  I've tried various line, bar, Gantt charts but haven't found a close solution. 

Thanks for any guidance!

[colettb]

Hi @Irwan!

I'm happy to give some sample data.  I've added teh start and end date columns you suggested.  I would need to have some sort of mark during the start-end period where the 3 milestones occurred. 

[Irwan · Accepted solution]

Hello @colettb 

i would do something like this.

1. create list of date from Start Date up to End Date

2. create calendar table

3. create relationship between two table

4. create a measure define value (this makes the width of column is not too long or else the value will be date format)

Value =
IF(
    not ISBLANK(SELECTEDVALUE('Table'[Custom.1])),
    1,
    0
)

5. create two measure for conditonal formating (make background color red and make value transparant)

Color Background =
IF(
    [Value]=1,
    "#FF0000"
)

Color Font =
IF(
    not ISBLANK([Value]),
    "#FFFFFF00"
)

5. assign conditional formatting for background and font

6. create a calculated column to check whether the list of date coincide with any of milestone date

7. assign conditional formating icon

Detail is in pbix.

Hope this will help.

Thank you.

[colettb]

Thank you @Irwan!  That does get me closer.  I understand the need for start and end dates for each row.  Once I get that done, what type of visualization would allow me to plot the 3 important dates and have them called out plainly?  Any suggestions?

[Irwan]

hello @colettb 

it would be great if you can give a sample of your original data.

but as far as i know, the only default visual that can do your screenshot is matrix.

however, matrix only accepts 100column so you can have only up to 3month if daily (3x30days).

Thank you.

[Irwan]

hello @colettb 

in my case, i created a multiple date in power query because in power bi you need a value in those days to be filled.

from this value

becomes this value

using this m-code

Table.AddColumn("Previous Step", "Custom", each Table.AddColumn(#"Filled Down", "Custom", each {Number.From([Start])..Number.From([End])}))

then you can plot in matrix visual to get that values in between two dates.

Hope this will help.

Thank you.