Controlling the steps achieved on a process for a particular request

[luzrueda]

Hello,

I have a 4 step process for different requests (a and b in the table). The process should be completed in the order from 1 to 4. That means that to complete 3 I need to complete 2 before. The last step with no completion date should be the active step. The problem is that I am missing dates in between. For example, for request (a) I don't have the date when step 2 was completed and therefore it appears to be active, but I can see when step 3 was completed. In that case I want to get a state where if the last step (step 4) is active, all other steps appear as completed. But if that step is not active everything should keep the status (completed - if there is a date, Active - if the previous date is available, and waiting if the previous date is not available. 

I have tried the following formula:

IF(AND(Table[Processstep]="4",table[Fortschritt]="active"), "Active", IF(Table[Processstep]<>"4","completed",table[status])

However that formula, wil work for request a, but not for request b. I will be very thankful if you can give me an idea on how to solve that.

Request	Process step	Date Completed	Status	Wanted Status
a	1	10.05.2022	Completed	Completed
a	2		Active	Completed
a	3	23.06.2022	Completed	Completed
a	4		Active	Active
b	1	10.10.2022	Completed	Completed
b	2		Active	Active
b	3		Waiting	Waiting
b	4		Waiting	Waiting

[amitchandak · Accepted solution]

@luzrueda , Try a new column

New column =
var _max = maxx(filter(Table, [Request] = earlier([Request]) && [Status] = "Completed"), [Process step])+0
return
if([Process step] <=_max, "Completed", [Process step])

[luzrueda]

Thank you so much @amitchandak that was a great solution!