Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting row values from another row based on associated texts

Hi   I am trying to get the calculated value based on these relationships:   Project Initiation Phase - M02 Scope and Feasibility - M04 Design Phase - M12 Delivery Phase - M23 Handover/Takeov...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this new code to add a column:

    Calculated Value = 
VAR _A =
    SWITCH (
        TRUE (),
        [ObjectName] = "Project Initiation Phase", "M02 - Approve Fee Proposal",
        [ObjectName] = "Scope and Feasibility", "M04 - Approve Phase 1 Report",
        [ObjectName] = "Design Phase", "M12 - Approve Phase 2 Report",
        [ObjectName] = "Delivery Phase", "M23 - Issue Certificate of Completion",
        [ObjectName] = "Hand Over/Take Over Phase", "M24 - Recommend Hand Over Acceptance",
        [ObjectName] = "Project Close Phase", "M25 - Reduce PO Values To Zero",
        [ObjectName] = "Scope and Feasibility Phase", "Scope and Feasibility Phase",
        BLANK ()
    )
VAR _B =
    IF (
        ISBLANK ( _A ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[ActualFinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        )
    )
RETURN
    IF (
        ISBLANK ( _B ),
        CALCULATE (
            MAX ( 'Table'[FinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        ),
        _B
    )

     

     

     

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Essentially, I am trying to get the actual finish date from all the M labels i.e. M02, M04, M23 etc into the various phases. One example would be getting the M02 -actual finished into the project initation phase for project name 1000- so it would be 05/02/2018 as the newly calculated field in Project Initiation Phase.

 

Hope this is clear. 

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Anonymous 

 

Try this code to add a new column to your table:

Calculated Value =
VAR _A =
    SWITCH (
        TRUE (),
        [ObjectName] = "Project Initiation Phase", "M02",
        [ObjectName] = "Scope and Feasibility", "M04",
        [ObjectName] = "Design Phase", "M12",
        [ObjectName] = "Delivery Phase", "M23",
        [ObjectName] = "Hand Over/Take Over Phase", "M24",
        [ObjectName] = "Project Close Phase", "M25",
        [ObjectName] = "Scope and Feasibility Phase", "Scope and Feasibility Phase",
        BLANK ()
    )
RETURN
    IF (
        ISBLANK ( _A ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[ActualFinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        )
    )

 

Output:

 

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Vahid-that was great!

     

    However, I was testing the data and had another issue you might be able to help. Sometimes, the data will come up with 00/00/0000 for the actual finish and then I have to search for another finish date field. In this example, when project close for project name = 2000, the value is 00/00/0000, so I will have to use another date field (column 5) in M25.. so the calculated field should become 11/10/2021 instead of 00/00/0000 for project 2000 during project close. 

     

    Hope this is clear.

     

     

    ProjectNameObjectNameForecastFinishDateActualFinishDateFinishDateCalculated
    1000Delivery Phase20/05/202027/08/202030.04.202030/04/2020
    1000Design Phase14/06/201911/06/201911.06.201928/05/2019
    1000Hand Over/Take Over Phase10/12/20194/02/202124.02.20214/02/2021
    1000M02 - Approve Fee Proposal00/00/00005/02/201805.02.2018 
    1000M04 - Approve Phase 1 Report00/00/000012/07/201812.07.2018 
    1000M12 - Approve Phase 2 Report00/00/000028/05/201911.06.2019 
    1000M23 - Issue Certificate of Completion00/00/000030/04/202030.04.2020 
    1000M24 - Recommend Hand Over Acceptance00/00/00004/02/202124.02.2021 
    1000M25 - Reduce PO Values To Zero00/00/00004/02/202111.03.2021 
    1000Project Close Phase10/12/201900/00/000030.04.20214/02/2021
    1000Project Initiation Phase23/01/20185/02/201805.02.20185/02/2018
    1000Scope and Feasibility Phase28/05/201812/07/201812.07.201812/07/2018
    2000Delivery Phase25/11/201920/11/201920.11.201920/11/2019
    2000Design Phase10/12/20166/04/201806.04.20186/04/2018
    2000Hand Over/Take Over Phase30/09/201923/07/202021.02.20204/08/2020
    2000M02 - Approve Fee Proposal00/00/00001/06/201501.06.2015 
    2000M04 - Approve Phase 1 Report00/00/000022/10/201624.10.2016 
    2000M12 - Approve Phase 2 Report00/00/00006/04/201806.04.2018 
    2000M23 - Issue Certificate of Completion00/00/000020/11/201920.11.2019 
    2000M24 - Recommend Hand Over Acceptance00/00/00004/08/202021.02.2020 
    2000M25 - Reduce PO Values To Zero00/00/000000/00/000011.10.2021 
    2000Project Close Phase22/10/202000/00/000019.02.202100/00/0000
    2000Project Initiation Phase1/06/20151/06/201501.06.20151/06/2015
    2000Scope and Feasibility Phase22/10/201622/10/201624.10.201622/10/2016
    3000………… 
    3000………… 

     

     

     

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      How did you find that 11/10/2021 instead of 00/00/0000 for project close project 2000? there is no date like that in your table?

       

      can you add more details?

       

      If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
      Appreciate your Kudos!!
      LinkedIn:       www.linkedin.com/in/vahid-dm/

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Vahid,

         

        Can you see the updated dataset I have provided you above?