Avoid Circular Dependency

[Sophie_Julien]

Hi!

I need help in avoiding a circular dependency in Power BI.

I have 2 measures that are creating a "circular dependency" and I don't know how to avoid this. I'm trying to calculate my current, past as well as future "Total inventory" by saying if my "DC OH Inventory" = 0, use my "Excess Short" inventory for the previous week to predict the current/future "total inventory". Otherwise, If i have data against DC inventory, my total inventory should equal to DC OH + Store OH + LE MSR Qty.

However, my excess/short inventory will equal to my total inventory (current and predicted) - POS Sales.

Below are the measures I'm trying to buil in my table:

Total Inventory:

Total Inventory = if(sum('Customer Inventory'[DC OH (Unit)]) =0, CALCULATE([Excess/Short],FILTER(ALL('Calendar'), 'Calendar'[Week] = MAX('Calendar'[Week])-1),FILTER(ALL('Calendar'), 'Calendar'[Year] = 'Calendar'[Year])) + SUM('LE MSR Qty'[LE MSR Qty (Unit)]),SUM('Customer Inventory'[DC OH (Unit)]) + SUM('Customer Inventory'[Store OH]) + sum('LE MSR Qty'[LE MSR Qty (Unit)]))

Excess/Short inventory:

Excess/Short = [Total Inventory] - SUM('POS Data'[Avg POS Unit Sold])

Thanks so much!!

[dethompson97]

This article helped me tremendously when I was trying to understand this topic.

https://www.sqlbi.com/articles/understanding-circular-dependencies/

Thanks!

Dawn

PS:  If this solution works, please mark it as a solution to help others. Kudos are nice too.

[Sophie_Julien]

Thanks Dawn,

It was a great article but didn't help me resolve my issue unfortunately.

I was hoping I could get some advise on my formula as it's where i feel i'm doing something wrong.

thanks,