Forum Discussion
Add milestone dates to a Matrix/Gantt Table?
Hello,
QUESTION:
Is there a way I could return milestone dates in the Matrix/Gantt chart below? My goal is to show the Project Status and list the date of review every time there’s a project review that occurs.
CONTEXT:
I watched this wonderful tutorial on turning a Matrix table into a Gantt chart so that dates within the Project period are highlighted. (For anyone interested, this is the video: https://www.youtube.com/watch?v=QugkBqc-jIs) I used the measure below to create a Matrix table, so that it shows the dates in my project from start to end. Snip of Measure + Matrix shown below.
DATA:
In my data, I have projects with milestone/review dates – some with multiple rows if they have multiple review dates (these are in blue font in the example below). I created a date table with Calendarauto() to link to it. Snip of data table + data model below
Project | Start Date | End Date | Review Date | Project Status |
Project A | 10/6/2018 | 5/23/2030 | 4/18/2023 | Red |
Project A | 10/6/2018 | 5/23/2030 | 7/7/2023 | Green |
Project B | 10/13/2021 | 7/4/2023 | 5/12/2023 | Yellow |
Project C | 4/27/2022 | 12/6/2028 | 4/15/2023 | Green |
Project D | 4/29/2023 | 11/18/2025 | 6/15/2023 | Red |
Project E | 6/23/2023 | 6/25/2029 | 7/1/2023 | Yellow |
Project F | 6/25/2017 | 3/3/2027 | 3/19/2023 | Red |
Project G | 5/15/2022 | 9/14/2023 | 4/15/2023 | Green |
Project H | 11/9/2021 | 4/5/2030 | 3/15/2023 | Green |
Project I | 7/27/2020 | 12/18/2027 | 7/1/2023 | Green |
Project J | 7/18/2018 | 2/12/2024 | 5/6/2023 | Yellow |
Project K | 12/16/2018 | 12/21/2023 | 3/3/2023 | Green |
Project L | 3/23/2023 | 7/8/2023 | 5/25/2023 | Red |
Project M | 9/11/2019 | 12/30/2026 | 7/4/2023 | Green |
Project N | 9/30/2020 | 8/9/2029 | 4/3/2023 | Green |
Project O | 12/15/2018 | 3/11/2024 | 3/28/2023 | Yellow |
Project O | 12/15/2018 | 3/11/2024 | 5/18/2023 | Yellow |
Project P | 1/11/2020 | 8/30/2027 | 5/16/2023 | Yellow |
Project Q | 5/25/2022 | 6/12/2028 | 5/30/2023 | Yellow |
Project R | 8/8/2021 | 4/26/2026 | 5/27/2023 | Yellow |
Project S | 5/14/2018 | 6/7/2025 | 4/1/2023 | Yellow |
Project T | 2/10/2019 | 4/3/2025 | 5/4/2023 | Green |
Project U | 11/21/2021 | 11/26/2025 | 7/1/2023 | Green |
Project V | 11/8/2017 | 9/7/2027 | 3/11/2023 | Green |
Project W | 10/14/2021 | 12/19/2026 | 5/28/2023 | Green |
Project X | 3/17/2019 | 5/17/2023 | 3/21/2023 | Green |
Project Y | 12/29/2022 | 7/25/2023 | 3/13/2023 | Green |
I would like to show these dates on the Matrix/Gantt chart above along with conditional formatting for their project status, so it looks like the chart below, but with the project period as well.
Is this possible? If so, any ideas on how to achieve this?
Thank you!
2 Replies
dashtesttest , Please find the attached file below, after signature
Refer video, how to use conditional formmating
Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs
Hi amitchandak thank you for your response!
I had actually watched your video last week - it was the one that made me think all this could be possible. I had tried the "Project Cnt" formula provided in your video as well as variations of it; however, the expressions didn't work to convert to a scalar value - probably due to the multiple review dates/milestone I have in my data.
I'm trying to see if there is a way to include the review dates/milestones, so that they appear within the project period like in this example below. Do you think this would be possible in a measure?I had used the following measure to make the project period (the blue lines in the snip above):
ProjectPeriod =VAR StartDate =CALCULATE(MIN(Table1[Start Date]),REMOVEFILTERS('Calendar'))VAR EndDate =CALCULATE(MAX('Table1'[End Date]),REMOVEFILTERS('Calendar'))VAR ProjectPeriod =MIN('Calendar'[Date])>=StartDate&& MIN('Calendar'[Date]) <= EndDateVAR ProjectStatus =CALCULATE(MIN('Table1'[Project Status]),REMOVEFILTERS('Calendar'))VAR Result =IF(ProjectPeriod, 1)RETURNResult
