DAX for previous day

Lu QIN 21 Reputation points
2022-10-17T09:16:58.24+00:00

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!

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,416 Reputation points
    2022-10-17T12:07:18.13+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful