Forum Discussion
Circular dependency error while creating two inter dependent columns from other columns
Please try these column expressions instead, replacing StockSales with your actual table name.
Opening Stock = var vThisDate = StockSales[Date]var vPurchases = CALCULATE(SUM(StockSales[Purchased Stock]), All(StockSales), StockSales[Date] <= vThisDate)var vSales = CALCULATE(SUM(StockSales[Sold Stock]), all(StockSales), StockSales[Date] < vThisDate)return vPurchases - vSalesClosing Stock = var vThisDate = StockSales[Date]var vPurchases = CALCULATE(SUM(StockSales[Purchased Stock]), all(StockSales), StockSales[Date] <= vThisDate)var vSales = CALCULATE(SUM(StockSales[Sold Stock]), All(StockSales), StockSales[Date] <= vThisDate)return vPurchases - vSalesPat
Please try these column expressions instead, replacing StockSales with your actual table name.
That worked like a charm. Thanks a lot mahoneypat.
Hi Pat ( mahoneypat ), Thanks for the help recently.
I have a scenario where, the data is like this...
which is clear that the purchased stock value is not available. But can be obtained and accounted for manually with the help of inventory department (which cannot be obtained while creating the above table). and for the first row in opening stock I can enter the opening stock value manually in Excel (as in the image below) but the same thing when I did in PowerBI, the things are breaking. and getting the negative values.
Moreover, there are categories of stock to be accounted in this data, for example there are 3 categories of stock, whose count need to be tracked on perday basis. (not mandatory that each category of stock is sold on a particular date and also not mandatory that when stock is purchased all categories of stock are purchased on a particular date).
I would be very thankful if you could help me obtain solution for these. And help from any community members is verymuch invited.
Thanks in Advance.
Would this approach work, adding in an initial value? It is hardcoded below but it could be a dynamic expression with a value from another table you bring in.
Opening Stock =
VAR vInitValue = 100 // or use dynamic expression
VAR vThisDate = StockSales[Date]
VAR vPurchases =
CALCULATE (
SUM ( StockSales[Purchased Stock] ),
ALL ( StockSales ),
StockSales[Date] <= vThisDate
)
VAR vSales =
CALCULATE (
SUM ( StockSales[Sold Stock] ),
ALL ( StockSales ),
StockSales[Date] < vThisDate
)
RETURN
vInitValue + vPurchases - vSalesPatThanks for the suggestion Pat ( mahoneypat ).
It certainly helped me and there were a few corrections to be made at other (prior) stages in my data. Now result is as expected after dealing with those corrections and changes.