Calculated column to change Names

[harshagraj]

Hi all, Please find the below table. I need a calculated column and below is the requirement.

If the Gate is G3 and Recommendation is Approve The Gate remais same I,e G3.
If after G3 when the project moves to G4 and if Recommendation is Interim Review then G4 becomes G3 and the previous G3 becomes G2.
and same happens If G3 is Approve and If G4 is Approve and G5 is Interim Review then G5 becomes G4 and G4 becomes G3 and G3 becomes G2..
Please help me with this.

project_numberGateTypeRecommendationDates

PRJ-14	G3	Custom Field	Approve	02-03-2016 00:00
PRJ-14	G3	Custom Field	Interim Review	16-02-2016 00:00
PRJ-14	G3	Schedule	Approve	02-03-2016 00:00
PRJ-14	G3	Schedule	Interim Review	16-02-2016 00:00
PRJ-14	G4	Custom Field	Interim Review	10-05-2016 00:00
PRJ-14	G4	Custom Field	Interim Review	08-07-2016 00:00
PRJ-14	G4	Custom Field	Interim Review	16-08-2016 00:00
PRJ-14	G4	Schedule	Interim Review	10-05-2016 00:00
PRJ-14	G4	Schedule	Interim Review	08-07-2016 00:00
PRJ-14	G4	Schedule	Interim Review	16-08-2016 00:00
PRJ-14	G4	Schedule	Interim Review	03-04-2018 00:00
PRJ-14	G4	Schedule	Interim Review	12-10-2018 00:00
PRJ-14	G4	Schedule	Interim Review	27-02-2020 00:00

[Anonymous]

Hi @harshagraj ,

What is your expected result? Whether the field "Updated_Gate" in below screen shot is the result what you desired?

Best Regards

Rena

[harshagraj]

Hello @yingyinr thank you for the answer. Yes, it's right, I need a separate column. Thank you in advance.

[Anonymous]

Hi @harshagraj ,

Whether the value of field "Updated_Gate" in my previous post is correct? The more sample data and details are required, they need to show all the situations you mentioned. And how to know which door is the previous one of the current door?

Best Regards

Rena

[harshagraj]

Hi @Anonymous yes u were correct with the column you mentioned. That is what i need for. Actually its based on the Project numbers.Intially it starts from G1-G6. But our scope is grom G3 only. As we are not bothered about G1- G2 gates. The thing is due to data restictions i cant upload the full data. Please please let me know how i can help you further.

[Anonymous · Accepted solution]

Hi @harshagraj ,

You can create one calculated column as below:

Calculated = if('Table'[Recommendation]="Approve",'Table'[Gate],CALCULATE(max('Table'[Gate]),FILTER('Table','Table'[Gate]<EARLIER('Table'[Gate]))))

Best Regards

Rena

[harshagraj]

Hi @Anonymous thanks a lot for the solution you provided.
It was working absolutely fine. Now i have a small problem. Please see the screenshot below The calculation you provided me is Calculated Gate Column.  It is showing 3 rows instead of one. Please help me. 2020 Project Gate has G5 and Calculated Gate should only have G5. 

[harshagraj]

Hi @Anonymous thank you for the responce. Yes you are correct i need a seperate column. Thanks in advance.