power bi lookup value previous date

[kenmuir71]

Hi I cannot work out how to do the following in Power BI. I need to create a column with a value of the position of the previous day based on the product ID.

I have a large table of product IDs. Each date a product ID has a position. The position ID's are unique for each day, but the ProductID may be present in mutiple PositionID's, so are not unique.

So in the example below Product ID 5284 is in position 10019025 o the 5th Dec. I want the new column to return the position of the previous day for that product ID, which is 10019023. I have been attempting to achieve this with Lookup Value but with no success, any suggestions welcome! 

[V-pazhen-msft · Accepted solution]

@kenmuir71 

Column = CALCULATE(MAX(Sheet2[Position]),ALLEXCEPT(Table,Table[ProductID]),Table[Date]=EARLIER(Table[Date])-1)

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[az38]

Hi @kenmuir71 

try a column

Column = LOOKUPVALUE(Table1[Position];Table1[ProductId];Table1[ProductId];Table1[Date];Table1[Date]-1)

do not hesitate to give a kudo to useful posts and mark solutions as solution