cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomLU123
Helper III
Helper III

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

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 DateTaskStatusPrevious Week's Status
3/29/2019AWIP 
3/29/2019BWIP 
3/29/2019CWIP 
4/5/2019AWIPWIP
4/5/2019BWIPWIP
4/5/2019CDoneWIP
4/12/2019AWIPWIP
4/12/2019BDoneWIP
4/12/2019CDoneDone

 

Is it possible to achieve it by DAX?

 

Many thanks!

 

Best regards,

Tom

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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 ) 
)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
parry2k
Super User
Super User

@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 ) 
)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!