Forum Discussion

Aliwells632's avatar
Aliwells632
Regular Visitor
1 year ago
Solved

Single date in matrix gantt

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. 
Need Help
  • Aliwells632's avatar
    Aliwells632
    1 year ago

    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

     

     

6 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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's avatar
      Aliwells632
      Regular Visitor
      1 year ago

      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's avatar
        v-sgandrathi
        Community Support
        1 year ago

        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.