creating a timeline with 3 distinct dates in one visual

[dw700d]

I would like to create a timeline visual that depicts the status of two separate time frames on one bar. The visual would look similar to a stacked barchart. I am tracking 6 projects with varying dates. I have a start date  a deadline and an estimated end date.

Start Date	Deadline Date	Estimated End Date
12/30/2019	7/31/2022	12/31/2026
6/29/2020	1/31/2023	1/30/2027
12/31/2020	7/31/2023	4/30/2027
12/30/2019	7/31/2024	12/31/2026
6/29/2020	1/31/2025	1/30/2027
12/31/2020	7/31/2025	4/30/2027

I want to compare each project start date and deadline date to its estimated end date in one bar.

Currently I am able to compare on two bars such as what you see below. The green bar by reflects start date and deadline date. The read reflects start date and estimated end date.

But ideally I would like to compare the dates on one bar such as this picture below. The green is start end date and the red highlights estimated end date.

I am familiar with Gannt 2.2.3, gantt by maq software, timeline, and the gantt xviz chart. The only one that I saw that gave me the capability I am looking for was the gantt xviz chart and they are charging for that feature which excludes that option for me.

Does anyone have any suggestions?

[MFelix · Accepted solution]

Hi @dw700d ,

You can create a similar look to this using a matrix and a calendar table then use the following measure:

Measure =
VAR Start_Dates =
    IF (
        MAX ( CalendarTable[Date] ) <= MAX ( 'Table'[Deadline Date] )
            && MIN ( CalendarTable[Date] ) >= MAX ( 'Table'[Start Date  ] ),
        1
    )
VAR End_dates =
    IF (
        MAX ( CalendarTable[Date] ) >= MAX ( 'Table'[Deadline Date] )
            && MIN ( CalendarTable[Date] ) <= MAX ( 'Table'[ Estimated End Date] ),
        2
    )
RETURN
    SWITCH ( TRUE (), Start_Dates > End_dates, 1, Start_Dates < End_dates, 2, 3 )

Now just colour the 1, 2, 3 with a condittional formatting.

On the example I have there is only year but you can add months, quarters days and make an hierarchy on the columns of the matrix.

Check PBIX file attach

[MFelix · Accepted solution]

Hi @dw700d ,

You can create a similar look to this using a matrix and a calendar table then use the following measure:

Measure =
VAR Start_Dates =
    IF (
        MAX ( CalendarTable[Date] ) <= MAX ( 'Table'[Deadline Date] )
            && MIN ( CalendarTable[Date] ) >= MAX ( 'Table'[Start Date  ] ),
        1
    )
VAR End_dates =
    IF (
        MAX ( CalendarTable[Date] ) >= MAX ( 'Table'[Deadline Date] )
            && MIN ( CalendarTable[Date] ) <= MAX ( 'Table'[ Estimated End Date] ),
        2
    )
RETURN
    SWITCH ( TRUE (), Start_Dates > End_dates, 1, Start_Dates < End_dates, 2, 3 )

Now just colour the 1, 2, 3 with a condittional formatting.

On the example I have there is only year but you can add months, quarters days and make an hierarchy on the columns of the matrix.

Check PBIX file attach

[dw700d]

Hi @MFelix  visual doesnt work as well as your unfortunately, its not returning any 2's

and I have used your Dax specifically. Any thoughts on why it isnt working for me?

[MFelix]

In the data you have given there were only dates I assumed that were different projects or lines. What type of data is the one on the rows of the matrix?

Can you share a mockup file please. 

[dw700d]

Thanks @MFelix  The data on the rows is text, it just includes the date in the name. I have attached my pbi file. Thank you for your help

https://drive.google.com/file/d/1Ed4bEhyH3I0HCvzYMZ722X9PrbuyzSL3/view?usp=sharing

[MFelix · Accepted solution]

Hi @dw700d ,

It's asking me for login on google drive can you please share it without password.

[dw700d]

@MFelix  thanks for your continued follow up.

I think I realize the issue. I am trying to filter my data by  the tracker field and the project field. When I attempt to filter the data by tracker it doesnt update. here is my link below. Thank you

https://drive.google.com/file/d/1Ed4bEhyH3I0HCvzYMZ722X9PrbuyzSL3/view?usp=sharing

[MFelix]

Hi @dw700d ,

The issue was with the column you are using for the project on your matrix, when having one to many relationship in your model you need to use always the column on the oneside for your visualization so in your case the sheet2[Project]

[dw700d]

@MFelix  Thank you so much for all the help and following up with me this works now

[dw700d]

Thank you I am taking a look now