Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @dw700d ,
It's asking me for login on google drive can you please share it without password.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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
Proud to be a Super User!
Check out my blog: Power BI em Português@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]
Regards
Miguel Félix
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |