Lookup a value from the next row in a group/partition

[bbdeveloper]

I am trying to create a calculated column "NextValidWW" which gets the next greater workweek partitioned by the ProjectID and TaskID. See below:

In SQL, I was able to achieve this by:

LEAD(StatusWW) 	OVER (
    PARTITION BY TaskUID, 
    ProjectID 	
    ORDER BY StatusWW
    )

I found a post on reddit where it gives a very similar solution, but I am not getting my desired outcome:

TRY 1:
This works when the "next" greater work week is only greater than the StatusWW by 1 - but does not account for cases when the "next" greater work week could be greater than 1.

=CALCULATE(        
    VALUES(DCN_NextWW[StatusWW]),
        ALLEXCEPT(DCN_NextWW, DCN_NextWW[ProjectID], DCN_NextWW[TaskUID]), 
        DCN_NextWW[StatusWW] = EARLIER(DCN_NextWW[StatusWW]) + 1 )

TRY 2:
This just gives me ERROR - which I understand it's returning all values that are greater than StatusWW - not just the "next" greater value

=CALCULATE(        
    VALUES(DCN_NextWW[StatusWW]),
        ALLEXCEPT(DCN_NextWW, DCN_NextWW[ProjectID], DCN_NextWW[TaskUID]), 
        DCN_NextWW[StatusWW] > EARLIER(DCN_NextWW[StatusWW]))

It seems like TRY 2 could use more conditions where [StatusWW] < the next [StatusWW] but I am not sure how to do that in DAX.. there is no NEXT() function that I am aware of

[bbdeveloper · Accepted solution]

Found a solution on another community post 

CALCULATE(
	MIN(DCN_NextWW[StatusWW]),
	FILTER(
	       DCN_NextWW,
	       DCN_NextWW[StatusWW] > EARLIER(DCN_NextWW[StatusWW]) && (DCN_NextWW[ProjectID] = EARLIER(DCN_NextWW[ProjectID]) && DCN_NextWW[TaskUID] = EARLIER(DCN_NextWW[TaskUID]))))