Forum Discussion

Luzadriana255's avatar
Luzadriana255
Icon for Helper II rankHelper II
3 years ago
Solved

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 "sta...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
Luzadriana255
Icon for Helper II rankHelper II
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    Luzadriana255
    Icon for Helper II rankHelper II
    3 years ago

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