Forum Discussion
Grab value from another row to current row value
Guys- have a question here.
I am trying to grab an Actual FinishDate value from each project and then reflect it in a new column/measure. In this example, for project ID 1000, I like to get the actual FinishDate from Phase Name [milestone 1] and insert it into Phase Name [Phase 1].
Then I will do that for project ID 2000 etc.
I will use the same logic for Phase 2, milestone 2 etc for subsequent projects.
Hi,
Please check the below picture.
It is for creating a new column.
Actual_Milestone CC =
VAR currentproject = Data[Project ID]
VAR currentphasenumber =
RIGHT ( Data[Phase Name], 1 )
VAR filter_only_milestone =
FILTER (
Data,
Data[Project ID] = currentproject
&& CONTAINSSTRING ( Data[Phase Name], "M" )
&& RIGHT ( Data[Phase Name], 1 ) = currentphasenumber
)
VAR milestonedate =
MAXX ( filter_only_milestone, Data[Actual Finishdate] )
RETURN
IF ( Data[Estimate Finishdate] = BLANK (), BLANK (), milestonedate )Hey! Anonymous
The following Calculated Column works perfectly fine.
NewCol =VAR rightval =RIGHT ( 'Table'[Phase Name], 1 )VAR maxval =CALCULATE (MAX ( 'Table'[Actual Date] ),FILTER (ALL ( 'Table' ),EARLIER ( 'Table'[ProjectID] ) = 'Table'[ProjectID]&& RIGHT ( 'Table'[Phase Name], 1 ) = rightval&& LEFT ( 'Table'[Phase Name], 1 ) = "M"))RETURNIF ( 'Table'[Estimate Date] = BLANK (), BLANK (), maxval )Please accept it as a solution if it fulfills your requirement.
5 Replies
Samarth_18Community Champion
Hi Anonymous ,
Please provide some sample data in a text format and expected output.
You can follow the below post to get your answer quickly:-
Thanks,
Samarth
- Jihwan_Kim
Super User
Hi,
Please check the below picture.
It is for creating a new column.
Actual_Milestone CC =
VAR currentproject = Data[Project ID]
VAR currentphasenumber =
RIGHT ( Data[Phase Name], 1 )
VAR filter_only_milestone =
FILTER (
Data,
Data[Project ID] = currentproject
&& CONTAINSSTRING ( Data[Phase Name], "M" )
&& RIGHT ( Data[Phase Name], 1 ) = currentphasenumber
)
VAR milestonedate =
MAXX ( filter_only_milestone, Data[Actual Finishdate] )
RETURN
IF ( Data[Estimate Finishdate] = BLANK (), BLANK (), milestonedate )Hi JH,
Thanks for your post. What if data is changed to the below?
ProjectNamePhase NameForecastFinishDateActualFinishDateCalculated Value
1000 Project Initiation Phase 23/01/2018 5/02/2018 5/02/2018 1000 M02XYZ 00/00/0000 5/02/2018 1000 Scope and Feasibility Phase 28/05/2018 12/07/2018 12/07/2018 1000 M04AGD 00/00/0000 12/07/2018 1000 Design Phase 14/06/2019 11/06/2019 28/05/2019 1000 M12ADS 00/00/0000 28/05/2019 1000 Delivery Phase 20/05/2020 27/08/2020 30/04/2020 1000 M23AAA 00/00/0000 30/04/2020 1000 Hand Over Phase 10/12/2019 4/02/2021 4/02/2021 1000 M24BBB 00/00/0000 4/02/2021 1000 Project Close Phase 10/12/2019 00/00/0000 4/02/2021 1000 M25CCC 00/00/0000 4/02/2021 2000 Delivery Phase 29/01/2019 00/00/0000 00/00/0000 2000 M23AAA 00/00/0000 00/00/0000 2000 Hand Over Phase 1/03/2019 00/00/0000 00/00/0000 2000 M24BBB 00/00/0000 00/00/0000 2000 Project Close Phase 29/02/2020 00/00/0000 00/00/0000 2000 M25CCC 00/00/0000 00/00/0000
Hey! Anonymous
The following Calculated Column works perfectly fine.
NewCol =VAR rightval =RIGHT ( 'Table'[Phase Name], 1 )VAR maxval =CALCULATE (MAX ( 'Table'[Actual Date] ),FILTER (ALL ( 'Table' ),EARLIER ( 'Table'[ProjectID] ) = 'Table'[ProjectID]&& RIGHT ( 'Table'[Phase Name], 1 ) = rightval&& LEFT ( 'Table'[Phase Name], 1 ) = "M"))RETURNIF ( 'Table'[Estimate Date] = BLANK (), BLANK (), maxval )Please accept it as a solution if it fulfills your requirement.Hi Shwet
what if data is changed to the following:
ProjectNamePhase NameForecastFinishDateActualFinishDateCalculated Value
1000 Project Initiation Phase 23/01/2018 5/02/2018 5/02/2018 1000 M02XYZ 00/00/0000 5/02/2018 1000 Scope and Feasibility Phase 28/05/2018 12/07/2018 12/07/2018 1000 M04AGD 00/00/0000 12/07/2018 1000 Design Phase 14/06/2019 11/06/2019 28/05/2019 1000 M12ADS 00/00/0000 28/05/2019 1000 Delivery Phase 20/05/2020 27/08/2020 30/04/2020 1000 M23AAA 00/00/0000 30/04/2020 1000 Hand Over Phase 10/12/2019 4/02/2021 4/02/2021 1000 M24BBB 00/00/0000 4/02/2021 1000 Project Close Phase 10/12/2019 00/00/0000 4/02/2021 1000 M25CCC 00/00/0000 4/02/2021 2000 Delivery Phase 29/01/2019 00/00/0000 00/00/0000 2000 M23AAA 00/00/0000 00/00/0000 2000 Hand Over Phase 1/03/2019 00/00/0000 00/00/0000 2000 M24BBB 00/00/0000 00/00/0000 2000 Project Close Phase 29/02/2020 00/00/0000 00/00/0000 2000 M25CCC 00/00/0000 00/00/0000