Forum Discussion
Circular dependency in inventory planning
We are currently working with a planning file that is built in Excel, and visualised in PowerBI. Since the Excel file is quite complex and causes issues, we are rebuilding a ‘light’ and more user-friendly version in PowerBI.
In the PowerBI file, we have exports from our software system on one hand, and a calculated table combining all relevant data. (+ a date table)
One of the things we want to visualise is the stock levels for the coming months.
In Excel, this is constructed in the following logic:
When we use the same approach in PowerBI, we get a circular dependency. (while working just fine in Excel)
We use the following functions for the unknown values:
Expected starting inventory:
= if('SOP data'[Date]>today(), lookupvalue('SOP data'[End Inventory],Masterdatafile[Item number],'SOP data'[Item number],'Date'[Date],'SOP data'[Date-1]),0)
Theoretical Starting inventory:
= if('SOP data'[Date]<=TODAY(),'SOP data'[starting inventory],'SOP data'[startinginventoryexpected])
End inventory:
= 'SOP data'[ Theoretical starting inventory]+'SOP data'[Net pickings]
(Net pickings = Sales forecast + purchase orders)
We tried to find a work-around to avoid this circular dependency, but we cannot find a solution.
5 Replies
- amitchandak
Super User
Anonymous , Expected starting inventory is using end inventory and end inventory uses it via Expected starting inventory
That is not allowed.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. amitchandak , thank you for your answer. We are trying to find a work around for this circular but can not find a working one. We hope to get ideas out of the community to solve our issue.
- amitchandak
Anonymous , Usually we build inventory like this on power bi
[Intial On Hand] + CALCULATE(SUM(Table[work order Qty]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[work order Qty]),filter(date,date[date] <=maxx(date,date[date])))
or
https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.We don't seem to make it work with your above suggestions.
Please find below link to Pbix doc with dummy data.Thanks in advance.
