Forum Discussion
Finding a date based on a related "Rang" parameter
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
Please refer to attached updated sample file with the proposed solutionEnda 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] )
9 Replies
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] )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!
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.
Luzadriana255
But what does it mean to have the End Date before the Start Date?
