Circular dependency in inventory planning

[StefDamen]

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.

[StefDamen]

@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]

@StefDamen , 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.

[StefDamen]

We don't seem to make it work with your above suggestions.

Please find below link to Pbix doc with dummy data.

link

Thanks in advance.

[V-lianl-msft]

Hi @StefDamen ,

I spent several hours trying to understand your model and find out the problem, but the model is complex and it is difficult to find the relationship between calculation table, calculation column and measure.

This is a good article on circular dependencies: https://www.sqlbi.com/articles/understanding-circular-dependencies/.

Hope thie will help you.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@StefDamen , 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.