Finding a date based on a related "Rang" parameter

[Luzadriana255]

Hello dear community,

I have two tables (process and parameter) connected by the step. I have different orders that perform a series of steps, some orders are missing some steps and therefore a "start date". I need to create an "End date" Column based on the next available step in the "Rang" column of the parameters table. For example, order 2345 follows the rang to the last available step, while order 4576 should skip the unavailable values and take the next available one as the "end date".

TABLE 1: Process

Order	Step	Start Date	End Date
2345	A	13.03.2020	02.04.2020
2345	B	02.04.2020	17.11.2020
2345	C	17.11.2020	01.01.2021
2345	D	01.01.2021	21.02.2021
2345	E	21.02.2021	01.03.2021
2345	F	01.03.2021	30.03.2021
2345	G	30.03.2021	30.03.2021
4576	A	11.08.2021	14.12.2021
4576	C	14.12.2021	22.01.2022
4576	D	22.01.2022	25.02.2022
4576	F	25.02.2022	25.02.2022

TABLE 2: Parameter

Step	Rang
A	1
B	2
C	3
D	4
E	5
F	6
G	7

I use the following formula:

End Date = LOOKUPVALUE

(Process[Start Date],

Parameter[Rang], related(Parameter[Rang])+1,

Process[Order], Process[Order],

 Process[Start Date])

I am i get the following results

Unfortunately, it does not work when the steps in between are missing. Could you please help me find the right formula for this case? Thank you so much!

[Luzadriana255]

Hi @tamerj1 

Thank you for your proposed Solution. Unfortunately I need to connect somehow the Rang, because the order have steps that are not mapped to the rang and also they do not follow the order of min to maximum date. I mean step B can have a larger date that Step C. Do you have an Idea on how to make this? Thank you!

[Luzadriana255]

@tamerj1 I reformulate the case, considering some aspects:

 I have two tables connected by the step, I have different orders performed thought a Serie of steps on an assigned date. The date is not organized from min to max, therefore the rang in table 2 is important. Some orders are missing some steps, and some steps are missing a date, which was fill up with the default value “01.01.2000” (e.g. Step D, order 2345). I need to create an End Date based on the next available step in the “Rang” mapped to the step in table 2. For example, the order 2345 follow the rang until the last available step, while the order 4576 jumps the unavailable values and take the next available as “End Date”. I am using the following formula:

End Date = LOOKUPVALUE

(Process[Start Date],

Parameter[Rang], related(Parameter[Rang])+1,

Process[Order], Process[Order],

 Process[Start Date])

Unfortunately, it does not work when the steps in between are missing. Could you please help me find the right formula for this case? Attached is the test data.

Important considerations

-It is important to consider that there are some steps are not considered in the rang (e.g Step B), therefore only the steps with a rang number should be used in the formula.

- In the last step (e.g. Rang 6) the Start Data and the End Data are the same.

TABLE 1

Order	Step	Start Date	End Date
2345	A	13.03.2020	02.04.2020
2345	B	02.04.2020	17.03.2020
2345	C	17.03.2020	01.01.2000
2345	D	01.01.2000	21.02.2021
2345	E	21.02.2021	01.03.2021
2345	F	01.03.2021	30.03.2021
2345	G	30.03.2021	30.03.2021
4576	A	11.08.2021	14.12.2021
4576	C	14.12.2021	22.01.2022
4576	D	22.01.2022	25.02.2022
4576	F	25.02.2022	25.02.2022

Table 2

Step	Rang
A	1
B
C	2
D	3
E	4
F	5
G	6

Thank you so much, I appreciate your help, I am sorry I did not iclude all the considerations before. 

[tamerj1]

@Luzadriana255 
But what does it mean to have the End Date before the Start Date?

[Luzadriana255]

@tamerj1 The problem is that we follow a process that is not well maintained in the system. We are trying to set it a way that we do not take into account those errors for the final result. I will be great if you have any suggestion for it. Thank you!

[tamerj1]

@Luzadriana255 
I'm just trying to put all together to understand the logic. 

B has no rang. While you mentioned that it should be ignored, the presented expected result for A considers the start of B but based on what? The start date? Then B has no Rang, yet the expected result for B is the Start Date for C. Again, based on what?! Even the start Date of C is before B's! In my opinion what governs the order is simply the Start Date and the Start Date only. All other parameters are just irrelevant. That brings us back to my original solution.

[Luzadriana255]

@tamerj1 I forgot those details when I modified the sample data (hier you find the table how it should be). the result of B is irrelevant because I am not considering it in my results, I consider filtering those steps that I do not need out in Power Query.  Still I need a formula that considers the rang and not the start date from min to max, since there can be errors and the date for steps without a date was set to "01.01.2000". Thank you for your understanding and time to clarify everything. 

TABLE 1

Order	Step	Start Date	End Date
2345	A	13.03.2020	05.03.2020
2345	B	02.04.2020(irrelevant)	02.04.2020 (irrelevant)
2345	C	05.03.2020	01.01.2000
2345	D	01.01.2000	21.02.2021
2345	E	21.02.2021	18.02.2021
2345	F	18.02.2021	30.03.2021
2345	G	30.03.2021	30.03.2021
4576	A	11.08.2021	14.12.2021
4576	C	14.12.2021	22.01.2022
4576	D	22.01.2022	25.02.2022
4576	F	25.02.2022	25.02.2022

Table 2

Step	Rang
A	1
B
C	2
D	3
E	4
F	5
G	6

[tamerj1 · Accepted solution]

@Luzadriana255 
Please refer to attached updated sample file with the proposed solution

Enda Date = 
VAR CurrentRang = RELATED ( Parameter[Rang] )
VAR Result =
    IF ( 
        CurrentRang <> BLANK ( ),
        MAXX ( 
            TOPN ( 
                1,
                FILTER ( 
                    CALCULATETABLE ( 
                        Process,
                        ALLEXCEPT ( Process, Process[Order] )
                    ),
                    RELATED ( Parameter[Rang] ) > CurrentRang
                ),
                RELATED ( Parameter[Rang] ),
                ASC
            ),
            Process[Start Date]
        )
    )
RETURN
    COALESCE ( Result, Process[Start Date] )

[Luzadriana255]

@tamerj1 Thank you so much for your help!This formula works perfectly 🙂

[tamerj1]

Hi @Luzadriana255 

Please refer to attached sample file with the proposed solution

Enda Date = 
VAR CurrentStart = Process[Start Date]
RETURN
    MINX ( 
        FILTER ( 
            CALCULATETABLE ( 
                VALUES ( Process[Start Date] ),
                ALLEXCEPT ( Process, Process[Order] )
            ),
            Process[Start Date] > CurrentStart
        ),
        Process[Start Date]
    )