Forum Discussion
A circular dependency was detected
Hello,
I have a problem to create stock balance. I have 3 tables connected: RM Stock, Monthly Demand & Purchase Order:
RM Stock LookUp
Monthly Demand LookUp
Purchase order LookUp
When I create the stock balance in Sep, the formula works fine but when I create the stock balance in Oct, it shows the error message:
<pi>A circular dependency was detected: TA_PurchaseOrder(supplier)_LookUp[Stock balance in Oct (sqm)], TA_PurchaseOrder(supplier)_LookUp[Stock balance in Sep(sqm)], TA_PurchaseOrder(supplier)_LookUp[Stock balance in Oct (sqm)].</pi>
Stock balance in Sep(sqm) = CALCULATE(SUM('TA_RM Stock_LookUp'[Total stocks m2])-CALCULATE(SUM('TA_Monthly Demand_LookUp'[Sep 2022]))+CALCULATE(SUM('TA_PurchaseOrder(supplier)_LookUp'[PO-Sep 2022])))
Stock balance in Oct (sqm) = CALCULATE(SUM('TA_PurchaseOrder(supplier)_LookUp'[Stock balance in Sep(sqm)])+CALCULATE(SUM('TA_PurchaseOrder(supplier)_LookUp'[PO-Oct 2022])-CALCULATE(SUM('TA_Monthly Demand_LookUp'[Oct 2022]))))
Could anyone help me to resolve this issue? Thank you.
1 Reply
- amitchandak
Super User
Anonymous , Looking a column, difficult to make out.
refer link: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
But The format of tables 2 and 3 is not correct and you should unpivot them and have common dimension between 3, date, product etc
You need to build measures like inventory
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
example
Qty On Hand = Calculate([Qty On Hand], all(Date[Date])) + CALCULATE(SUM(Table[Qty Sold]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Qty Expired]),filter(date,date[date] <=maxx(date,date[date])))
unpivot
