[Seek Help] How to Lookup the value of previous date in the same column?

[TomLU123]

Hi Expert,

I have a table like beow which shows the status of the Task as of each week. 

I want to create a new column "Previous Week's Status" which lookup the previous week Status of the task.

Report Effective Date	Task	Status	Previous Week's Status
3/29/2019	A	WIP
3/29/2019	B	WIP
3/29/2019	C	WIP
4/5/2019	A	WIP	WIP
4/5/2019	B	WIP	WIP
4/5/2019	C	Done	WIP
4/12/2019	A	WIP	WIP
4/12/2019	B	Done	WIP
4/12/2019	C	Done	Done

Is it possible to achieve it by DAX?

Many thanks!

Best regards,

Tom

[parry2k · Accepted solution]

@TomLU123 add following measure

Prev Week Status = 
VAR __prevDate = 
CALCULATE( 
    MAX( Table1[Report Effective Date] ),
    FILTER( 
        ALLEXCEPT( Table1, Table1[Task] ), 
        Table1[Report Effective Date] < MAX( Table1[Report Effective Date] )
    ) 
)
RETURN 
IF ( 
    ISBLANK ( __prevDate ), "", 
    CALCULATE( 
        MAX( Table1[Status] ), 
        ALLEXCEPT( Table1, Table1[Task] ), 
        Table1[Report Effective Date] = __prevDate ) 
)