cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dw700d
Helper V
Helper V

creating a timeline with 3 distinct dates in one visual

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/20201/31/20231/30/2027
12/31/20207/31/20234/30/2027
12/30/20197/31/202412/31/2026
6/29/20201/31/20251/30/2027
12/31/20207/31/20254/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.

 

Capture.PNG

 

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.

 

Capture1.PNG

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?

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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


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 @dw700d ,

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


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

9 REPLIES 9
MFelix
Super User
Super User

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


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 @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?

 

Capture.PNG

 

 

Capture1.PNG

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. 


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



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

Hi @dw700d ,

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


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

@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

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]

 

MFelix_0-1597764775085.png

 


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  Thank you so much for all the help and following up with me this works now

Thank you I am taking a look now

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors