Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Helper II
3 years ago

Gantt Chart Activity Tracking - Matrix Table

Hello,   I have created a matrix table, with measures to return a 1 or 0 value, and conditional formatting to colour the cells, to act as a gantt chart in my BI report.   Within this table there ...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
3 years ago

aha, are you able to share your pbix?

kristi_in_heels's avatar
kristi_in_heels
Helper II
3 years ago

Thank you for your response.

 

Unfortunately I'm not able to upload the .pbix file due to sensitive company information.

 

If it helps to further decipher I have the matrix set up, with conditional formatting applied to change the colours of the matrix bar based on the current project phase result they return from the measure "CF Gantt" taken from the youtube clip suggested above and modified to suit my project phases:

 

CF Gantt =
VAR Start_Date =
CALCULATE(
    MIN('Worsley FY24_25 Budget Tollgates'[PFS]),
    REMOVEFILTERS ('Calendar')
)
VAR End_Date =
CALCULATE(
    MIN('All Projects'[CloseOutFinishDate_Forecast]),
    REMOVEFILTERS('Calendar')
)
VAR ProjectPeriod =
    MIN('Calendar'[Date])>= Start_Date
    && MIN('Calendar'[Date])<= End_Date
VAR ProjectStatus =
CALCULATE(
    min('All Projects'[ProjectPhaseDescription]),
    removefilters('Calendar')
)
VAR Result =
SWITCH(
    TRUE(),
    ProjectPeriod && ProjectStatus = "Pre-feasibilty", 1,
    ProjectPeriod && ProjectStatus = "Feasibilty", 2,
    ProjectPeriod && ProjectStatus = "Execution", 3,
    ProjectPeriod && ProjectStatus = "Practical Completion", 4,
    ProjectPeriod && ProjectStatus = "Closeout", 5
)
RETURN
    Result

 

 

The colours are working, but this is colouring the entire project line on the matrix. 

 

Currently, all activities for a project are stretched across the one single line, with no distinguishment between.  There are the 5 project phases above, but within each phase, there is a separate set of dates for budgeted / planned / forecast / actual.

 

I would like to see either:

 

1) Each of the date bars as an individual subheading activity under the project as a header on an individual line, (20 in total per project) or;

 

2) Each of the date groups (Budget / Planned / Forecast / Actual) combined into one single line item as a subheader to show the life of project across the 5 project phases. 

 

Eg:

PROJECT

BUDGET DATES

  • PFS
  • FS
  • EXE
  • PC
  • CO

PLANNED DATES

  • PFS
  • FS
  • EXE
  • PC
  • CO

(...etc)

 

Alternatively, if we can colour code each of the project activities, leaving them on the same line as currently set up, this would help to show progress instead.

 

Any tips are appreciated.

 

Closer snip of the Matrix set up:

 

 

 

Thank you