Schedule Gantt Query - Matrix

[hackfifi]

Hello Good Day

I am using the below formula to develop a GANTT Chart in Matrix Visual. As you can see below, some "Activity IDs" do not have either a START or FINISH date i.e. it is BLANK. By using the below formula, the value "1" is shown continously (highligted in YELLOW). I only want to show the "1" on 07-Apr-24 (in the below screenshot example). Actually i would like to show it as "2" to put a different conditional formatting.

Thanks in Advance for your assistance.

Schedule_Gant =

var _startdate =

    CALCULATE(

        min(Schedule_Gant[Start_EOW]),REMOVEFILTERS('_Calendar Schedule')

    )

var _enddate =

    CALCULATE(

        Max(Schedule_Gant[Finish_EOW]),REMOVEFILTERS('_Calendar Schedule'))

var _period =

    min('_Calendar Schedule'[Weekly Cut-Off])>=_startdate

    && max ('_Calendar Schedule'[Weekly Cut-Off])<=_enddate

RETURN

if(_period,1)

[MFelix · Accepted solution]

Hi @hackfifi ,

For this you need to have a calculation that checks if the start date is blank try the following code:

Schedule_Gant =
VAR _startdate =
    CALCULATE (
        MIN ( Schedule_Gant[Start_EOW] ),
        REMOVEFILTERS ( '_Calendar Schedule' )
    )
VAR _enddate =
    CALCULATE (
        MAX ( Schedule_Gant[Finish_EOW] ),
        REMOVEFILTERS ( '_Calendar Schedule' )
    )
VAR _period =
    MIN ( '_Calendar Schedule'[Weekly Cut-Off] ) >= _startdate
        && MAX ( '_Calendar Schedule'[Weekly Cut-Off] ) <= _enddate
VAR _finalPeriod =
    IF (
        _startdate = BLANK (),
        MAX ( '_Calendar Schedule'[Weekly Cut-Off] ) = _enddate,
        _period
    )
RETURN
    IF ( _finalperiod, 1 )

Be aware that this may need some changes because I have no data to test it out.