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] )
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 🙂