DAX for previous day

Question

I have a Surplus-Balance table with columns of Date, Surplus, and Product_name, and I want to calculate a new value called Balance of each product. Formula is Balance = Surplus of the product in the previous day .
Also, a Calendar table is created, and the two tables is conneted together.
However, because there are several products so the date in the Surplus table is not unique, thus the previousday syntax doesn't work.
What I can do if i want to select the corresponding value of each product in the previous day and fill it into the Balance column. Thank you!

Answer 1

If your data is perfect, you can use LOOKUPVALUE ( 'Surplus-Balance'[Surplus], 'Surplus-Balance'[Date], [Date] - 1, 'Surplus-Balance'[Product_name], [Product_name] ) for your calculated column formula.