Gantt Matrix - How do I show multiple project phases in a single row and color format each phase?

[danny_b]

Hello,

I have built a gantt chart using a matrix visual, however, each project has multiple phases on different dates.  My data is below and I also have a date table I'm referencing:

New Project Table

Project	Phase	First Date	Last Date
Project 1	Pilot	5/15/2024	6/14/2024
Project 2	Pilot	10/24/2024	2/1/2025
Project 3	Pilot	12/3/2024	2/17/2025
Project 1	Market	1/1/2025	1/29/2025
Project 2	Market	2/3/2025	3/26/2025
Project 4	Pilot	2/10/2025	3/12/2025
Project 4	Market	4/10/2025	5/10/2025
Project 3	Market	5/20/2025	8/25/2025
Project 5	Pilot	5/29/2025	7/3/2025
Project 4	National	6/10/2025	7/10/2025
Project 5	Market	7/17/2025	9/18/2025

I initially built the below gantt using a summarized project table with overall start/end dates with the following result.

Project Timelines

Project	First Date	Last Date
Project 1	5/15/2024	1/29/2025
Project 2	10/24/2024	3/26/2025
Project 3	12/3/2024	8/25/2025
Project 4	2/10/2025	7/10/2025
Project 5	5/29/2025	9/18/2025

Values measure is below, and I then conditionally formatted the background color and the font.  

CF Gantt =

VAR StartDate =

    CALCULATE(MIN('Project Timelines'[First Date]), REMOVEFILTERS('Fiscal Calendar'))

VAR EndDate =

    CALCULATE(MIN('Project Timelines'[Last Date]), REMOVEFILTERS('Fiscal Calendar'))

VAR ProjectPeriod =

    MIN('Fiscal Calendar'[Calendar Date]) >= StartDate

    && MIN('Fiscal Calendar'[Calendar Date]) <= EndDate

VAR Result =

    IF(ProjectPeriod, 1)

RETURN

Result  

I would like to use the first table (New Project Table) to build a gantt matrix like above, however, I want to see the gaps between each phase, AND I'd like to see each phase in a different color like below:

I have tried adding a variable to the measure above to try to identify which phase the project is in (ProjectPeriod && Phase), but it only allows me to calculate the MIN or MAX.  How do I get it to recognize the ProjectPeriod AND Phase?

Thanks,

Daniel

[Anonymous · Accepted solution]

Thanks for the reply from bhanu_gautam , please allow me to provide another insight:

Hi, @danny_b 
Thanks for reaching out to the Microsoft fabric community forum.

My idea is similar to bhanu_gautam's.Regarding the issue you raised, my solution is as follows:

1.First, I created the following calculated table to serve as the columns of the matrix:

Fiscal Calendar = 
CALENDAR (
    MINX (
        {
            MIN ( 'New Project Table'[First Date] ),
            MIN ( 'New Project Table'[Last Date] )
        },
        [Value]
    ),
    MAXX (
        {
            MAX ( 'New Project Table'[First Date] ),
            MAX ( 'New Project Table'[Last Date] )
        },
        [Value]
    )
)

2.Next, I used the following measures as the values of the matrix:

Measure = 
VAR market1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR market2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Market"
        )
    )
VAR Pilot1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR Pilot2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "Pilot"
        )
    )
VAR National1m =
    CALCULATE (
        MIN ( 'New Project Table'[First Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
VAR National2m =
    CALCULATE (
        MAX ( 'New Project Table'[Last Date] ),
        FILTER (
            ALLEXCEPT ( 'New Project Table', 'New Project Table'[Project] ),
            'New Project Table'[Phase] = "National"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Fiscal Calendar'[Date] ) >= market1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= market2m, "#FF0000",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= Pilot1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= Pilot2m, "#00FF00",
        
        MAX ( 'Fiscal Calendar'[Date] ) >= National1m
            && MAX ( 'Fiscal Calendar'[Date] ) <= National2m, "#0000FF",
        
        "#FFFFFF" 
    )

3.Then, I modified the background colour:

4.Here's my final result, which I hope meets your requirements.

5.You may need to note that the matrix's column display has a 100-row limit. For more details, please refer to the documentation:

Solved: Missing Columns in Matrix - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

[bhanu_gautam]

@danny_b You need to create a measure that checks if a date falls within the start and end dates of each phase. 

DAX
CF Gantt Phase =
VAR CurrentDate = MIN('Fiscal Calendar'[Calendar Date])
VAR Phase1Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "Pilot")
VAR Phase1End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "Pilot")
VAR Phase2Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "Market")
VAR Phase2End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "Market")
VAR Phase3Start = CALCULATE(MIN('New Project Table'[First Date]), 'New Project Table'[Phase] = "National")
VAR Phase3End = CALCULATE(MAX('New Project Table'[Last Date]), 'New Project Table'[Phase] = "National")

RETURN
SWITCH(
TRUE(),
CurrentDate >= Phase1Start && CurrentDate <= Phase1End, 1,
CurrentDate >= Phase2Start && CurrentDate <= Phase2End, 2,
CurrentDate >= Phase3Start && CurrentDate <= Phase3End, 3,
BLANK()
)

Use the measure CF Gantt Phase to apply conditional formatting in the matrix visual. You can set different colors for the values 1, 2, and 3, which correspond to different phases.