Matrix as Gantt Chart with multiple Starting and End Dates for same row

[T800]

Dear PBI Community,
let me explain my problem:

I do have 2 Tables: 
1. Is Containing Projects with Start and End Dates (which i can show and have no problems with)

2. Is Containing the project phases assigned to a person again with Start and End Dates

you can see by the following screenshot (ProjectStart and EndDate shown as LookUp here)

The Matrix Gantt Chart looks like this right now (pls ignore pink and light pink cells):

You see that i am only able to show in darkblue colour the earliest (MIN) and the last (MAX) date entered. BUT  i am missing the phase in August (row 3) since it is in the middle. My question is: How can i show ALL entered phases DateRanges in the same row?

Project + Phase + Responsible are the same but differ from Phase Start and Phase End Dates.

Here my DAX Measure i am using and giving conditional formatting on:

CF Gantt =

// Projects

VAR StartDate =

CALCULATE(

MIN('Total_Report'[StartDate]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR EndDate =

CALCULATE(

MIN('Total_Report'[EndDate]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR ProjectPeriod =

MIN(Dim_M_Calendar[Date])>=StartDate && MIN(Dim_M_Calendar[Date]) <= EndDate

VAR Result =

IF(ProjectPeriod,1,0)

// ProjectPHASES

VAR StartDatePhaseMIN =

CALCULATE(

MIN('Total_Report'[StartDatePhase - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR EndDatePhaseMIN =

CALCULATE(

MIN('Total_Report'[EndDatePhase - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR StartDatePhaseMAX =

CALCULATE(

MAX('Total_Report'[StartDatePhase - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR EndDatePhaseMAX =

CALCULATE(

MAX('Total_Report'[EndDatePhase - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR PhasePeriodMIN = CALCULATE(

MIN(Dim_M_Calendar[Date])>=StartDatePhaseMIN && MIN(Dim_M_Calendar[Date]) <= EndDatePhaseMIN)

VAR PhasePeriodMAX = CALCULATE(

MAX(Dim_M_Calendar[Date])>=StartDatePhaseMAX && MAX(Dim_M_Calendar[Date]) <= EndDatePhaseMAX)

VAR Result2 =

IF(PhasePeriodMIN || PhasePeriodMAX,1,0)

// Absences

VAR StartDateAbsenceMIN =

CALCULATE(

MIN('Total_Report'[StartDateUrlaub - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR EndDateAbsenceMIN =

CALCULATE(

MIN('Total_Report'[EndDateUrlaub - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR StartDateAbsenceMAX =

CALCULATE(

MAX('Total_Report'[StartDateUrlaub - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR EndDateAbsenceMAX =

CALCULATE(

MAX('Total_Report'[EndDateUrlaub - Month]),

REMOVEFILTERS(Dim_M_Calendar)

)

VAR AbsencePeriodMIN =

MIN(Dim_M_Calendar[Date])>=StartDateAbsenceMIN && MIN(Dim_M_Calendar[Date]) <= EndDateAbsenceMIN

VAR AbsencePeriodMAX =

MAX(Dim_M_Calendar[Date])>=StartDateAbsenceMAX && MAX(Dim_M_Calendar[Date]) <= EndDateAbsenceMAX

VAR Result3 =

IF(AbsencePeriodMIN || AbsencePeriodMAX,3,0)

//Zusammenfassen der Resultate

VAR FinalResult = Result + Result2 + Result3

Return

FinalResult

 

*******************************

You can ignore the absences (pink ones) since it is exactly the same problem like with the phases.

Thanks in advance and kind regards

[MFelix]

Hi @T800 ,

To what I can see from your data the 3 rows that you present are all from the same projcet/phase so when you make a matrix grouping by project/phase and pick up the maximum (or minimum) from 3 rows you will get only a single value, in this case the 1st of January for the Phase start and the 31 of december for the phase end.

In this case you need to make a different comparision in order to get the phase in a different colour.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.