Forum Discussion

hackfifi's avatar
hackfifi
Helper V
3 years ago
Solved

Schedule Gantt Query - Matrix

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)

 

 

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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.

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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.