Forum Discussion
Calculated column to change Names
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 |
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
7 Replies
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
Hi Anonymous thank you for the responce. Yes you are correct i need a seperate column. Thanks in advance.
Hello @yingyinr thank you for the answer. Yes, it's right, I need a separate column. Thank you in advance.
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
