I need help to optimize Dax

[ThomasWeppler]

Hi power BI community
I have a tabel that looks at diffrent states a task can come through todostaehistory
For each row I want to see if the same task todostaehistory [todoid] has been accepted at a later stage.

So my first line look make sure that we only look at the tasks that are offers todostatehistory[stateid] = 13.

So each row where this isn't the case we can just put a blank value.

The problem really come with VAR nextDate

Here each row look thorugh each other row where the stateid = 13 and l

and find the next row on the same task todostatehistory[todoid] made at a later point in time and with a diffrent todostatehistory[stateid].

Here is the DAX:

Accepted =

IF (

    todostatehistory[stateid] <> 13,

    BLANK (),

    VAR currentDate = Todostatehistory[date]

    VAR currentState = todostatehistory[stateid]

    VAR nextDate =

        SELECTCOLUMNS (

            CALCULATETABLE (

                TOPN ( 1, todostatehistory, Todostatehistory[id] ),

                ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),

                todostatehistory[stateid] <> currentState,

                Todostatehistory[date] > currentDate

            ),

            "@val", Todostatehistory[date]

        )

    RETURN

        nextDate

)

My problem is that each row looks though each other row to find the right date. This means that the memory required to update my report grows exponential as I get more rows and that is obviously unsustainable.
Does any of your bright folks have an idea to how I can get around this issue?
All help is greatly appreciated.

[amitchandak · Accepted solution]

@ThomasWeppler , try like

new column =
Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[date] > earlier(Todostatehistory[date]) ), Todostatehistory[date])

Or break into two column
or create a rank first - new columns
rank = Rankx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] )), Todostatehistory[date],,asc,dense)

new column date=
Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[rank] > earlier(Todostatehistory[rank])+1 ), Todostatehistory[date])

[ThomasWeppler]

Thanks @amitchandak It worked perfectly. 
You are a Power BI God.