Single date in matrix gantt

[Aliwells632]

I have gantt chart using a matrix, it currently shows tasks and there complete dtae range. I know want to add some specific dates in against each task. For example the whole task runs from the 21/03/24-16/04/24 then the shipping bid needs to be in on th 01/02/24. I already have the dates in the table table i just need to know how to add them into the measure. 
This is the current measure;

CF Gantt1 =

VAR StartDate =

CALCULATE(

    MIN('20240529-DCC Task Planner'[Task start date]),

    REMOVEFILTERS(dimDate)

)

VAR EndDate =

CALCULATE(

    MIN('20240529-DCC Task Planner'[Task Finish date]),

    REMOVEFILTERS(dimDate)

)

VAR ProjectPeriod =

    MIN(dimDate[Date]) >= StartDate

    && MIN(dimDate[Date]) <= EndDate

VAR result =

IF(

ProjectPeriod,

1

)

RETURN

    Result
I want to achieve if the sea bid date if true then return a 2 inside this code so i can then conditional format it. 

[v-sgandrathi]

Hi @Aliwells632,

Thanks for using Microsoft Fabric Community Forum.

Your current measure determines whether a task's date range falls within the given start and end dates, returning 1 if true. You now want to incorporate a specific date (Sea Bid Date) so that when it matches a date in the matrix, the measure returns 2, allowing you to conditionally format it differently.

You need to check if the current date in the matrix (dimDate[Date]) matches the Sea Bid Date. If it does, return 2

Try this Updated Measure:

CF Gantt1 =

VAR StartDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Task start date]),

        REMOVEFILTERS(dimDate)

    )

VAR EndDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Task Finish date]),

        REMOVEFILTERS(dimDate)

    )

VAR SeaBidDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Sea Bid Date]),

        REMOVEFILTERS(dimDate)

    )

VAR ProjectPeriod =

    MIN(dimDate[Date]) >= StartDate &&

    MIN(dimDate[Date]) <= EndDate

VAR IsSeaBidDate =

    MIN(dimDate[Date]) = SeaBidDate

VAR Result =

    IF(IsSeaBidDate, 2, IF(ProjectPeriod, 1))

RETURN

    Result

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[Aliwells632 · Accepted solution]

So ive put that code, and changed it to the below ive even tried the code on its own. It doesnt return a value of 2. Ive set teh relationship to one to many. 

Highlight date =

VAR StartDate =

CALCULATE(

    MIN('20240529-DCC Task Planner'[Task start date]),

    REMOVEFILTERS(dimDate)

)

VAR EndDate =

CALCULATE(

    MIN('20240529-DCC Task Planner'[Task Finish date]),

    REMOVEFILTERS(dimDate)

)

VAR ProjectPeriod =

    MIN(dimDate[Date]) >= StartDate

    && MIN(dimDate[Date]) <= EndDate

VAR result1 =

IF(

ProjectPeriod,

1)

VAR AirbidDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Air Bid submission date(Deploy)]),

        REMOVEFILTERS(dimDate)

    )

VAR AirBid  =

    MIN(dimDate[Date]) = AirbidDate

VAR result2 =

IF(

AirBid,

2)

VAR FinalResult = result2 + result1

Return

FinalResult

[v-sgandrathi]

Hi @Aliwells632,

Thank you for trying the suggested solution and for your continued efforts.

To further troubleshoot, we recommend testing the measure in a table visual by adding dimDate[Date], StartDate, EndDate, and AirBidDate.

This will help verify if the values are aligning correctly for each task. If AirBidDate appears blank or doesn’t match expectations, it could point to data issues. Also, please double-check that the relationship between dimDate[Date] and '20240529-DCC Task Planner' is set up as a one-to-many active relationship. An incorrect or inactive relationship can prevent the matrix from evaluating the measure properly.

Try this Updated Measure:

Highlight Date =

VAR StartDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Task start date]),

        REMOVEFILTERS(dimDate)

    )

VAR EndDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Task Finish date]),

        REMOVEFILTERS(dimDate)

    )

VAR AirbidDate =

    CALCULATE(

        MIN('20240529-DCC Task Planner'[Air Bid submission date(Deploy)]),

        REMOVEFILTERS(dimDate)

    )

VAR CurrentDate = MIN(dimDate[Date])

RETURN

    IF(CurrentDate = AirbidDate, 2,

        IF(CurrentDate >= StartDate && CurrentDate <= EndDate, 1,

        BLANK()

    ))

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

[v-sgandrathi]

Hi @Aliwells632,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @Aliwells632,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @Aliwells632,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thank you.