Grab value from another row to current row value

[calvgo]

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.

[Jihwan_Kim · Accepted solution]

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 )

[Anonymous · Accepted solution]

Hey! @calvgo 

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"

)

)

RETURN

IF ( 'Table'[Estimate Date] = BLANK (), BLANK (), maxval )

Please accept it as a solution if it fulfills your requirement.

[calvgo]

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

[Jihwan_Kim · Accepted solution]

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 )

[calvgo]

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

[Samarth_18]

Hi @calvgo ,

Please provide some sample data in a text format and expected output.

You can follow the below post to get your answer quickly:-

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p...

Thanks,

Samarth