Calculating column based on the previous value of the column

[stefani_vileva]

Hello everyone,

I need help to calculate the stock of my product based on the values in the table and some previous stock that i have already stored in a measure. If the initial stock is 8 pieces, we have the following table, for example

Date	Product	Ordered	Sold	Stock
21.09.2023	A	0	30	-22
22.09.2023	A	90	0	90
23.09.2023	A	30	84	36
24.09.2023	A	0	40	-4
25.09.2023	A	0	31	-31
26.09.2023	A	126	39	87
27.09.2023	A	0	45	42

This means, if the stock on the particular day is negative, I ignore it in the next iteration and  in the next iteration I am using only the things that we have ordered, and again, if we have nothing ordered then in stock we have only the negative sold pieces.

I have tried solving the problem with using the previous quantity day but it doesn't work for the cases where I have left stock of the preprevious day.. I have also tried with rolling total but still it doesn't work.

Thank you in advance for your help.

Kind regards,

Stefani

[stefani_vileva · Accepted solution]

In the end, I found some dumb solution for the problem. Since I only calculate the stock for the next 7 days, I am using IF conditions to calculate the stock based on the conditions that I needed.

[123abc]

In Power BI, you can calculate the stock of your product based on the provided table using DAX (Data Analysis Expressions). You'll need to create a calculated column or measure to achieve this. Here's a step-by-step guide to calculate the stock:

Assuming you have a table named "Sales" with columns: Date, Product, Ordered, Sold, and you already have a measure for "Initial Stock," follow these steps:

1. Create a new calculated column for "Adjusted Sold" using the following formula. This column will replace negative stock values with zero:

Adjusted Sold = IF([Stock] < 0, 0, [Sold])

Create a new calculated column for "Stock" using the following formula. This column calculates the stock for each row based on the previous day's stock and the ordered quantity:

Stock = VAR CurrentDate = [Date] VAR PreviousDateStock = CALCULATE( SUM(Sales[Stock]), FILTER( Sales, Sales[Product] = EARLIER(Sales[Product]) && Sales[Date] < CurrentDate ) ) RETURN IF( CurrentDate = MIN(Sales[Date]), [Initial Stock] + [Ordered] - [Adjusted Sold], PreviousDateStock + [Ordered] - [Adjusted Sold] )

This formula does the following:

• It calculates the stock for the current row based on the previous day's stock (filtered by the product) and the ordered quantity for the current day minus the adjusted sold quantity.
• If the current row is the first row for a product (determined by comparing the current date to the minimum date for that product), it uses the "Initial Stock" instead of the previous day's stock.

1. Now, you have a "Stock" column that calculates the stock for each row in your table based on the rules you specified.

2. You can use this "Stock" column in your visualizations to analyze the stock over time.

Make sure to replace "Sales" with the actual name of your table, and adjust column and measure names as needed to match your Power BI model. This approach should handle cases where you have left stock from the preprevious day and ignore negative stock values in subsequent iterations.

[stefani_vileva]

Hello @123abc,

Thank you for your answer. Unfortunately, this solution doesn't work for me because it creates circular dependency in the background and doesn't let me to create the Stock calculated column.

Kind regards,

Stefani