how to remove A circular dependency

[hchauhan556]

I got the error of Circular dependency.

codes of two diff measures are. 

Inventory =

VAR CurrentMonthInventory = SUM(CurrentInventory[Quantity])

VAR LastMonthInventory = CALCULATE([Ending Inventory], PREVIOUSMONTH('Date'[Date]))

RETURN

IF(CurrentMonthInventory = 0, LastMonthInventory, CurrentMonthInventory)

other measure 

Ending Inventory =

VAR InvoiceQty = SUM('InvoiceVolumeReport'[Invoice Quantity])

VAR ForecastedQty = SUM('Demand'[ForecastedQty])

VAR AdjustedInventory1 =[Inventory]

VAR PackagingValue = SUM('Packaging'[Value])

RETURN

IF (

   InvoiceQty > ForecastedQty,

   AdjustedInventory1  + PackagingValue,

  AdjustedInventory1  - ForecastedQty + InvoiceQty + PackagingValue

)

ATTACHED SCREENSHOT HAS THE ERROR:

RELATIONS MA CREATING IS SHOWN IN AS BELOW:-

[Anonymous]

Hi, @hchauhan556 

To remove circular dependency, you can rewrite the calculation logic of LastMonthInventory in Inventory in Ending Inventory to avoid using Inventory directly. For example, redefine and use CurrentMonthInventory in Ending Inventory instead of AdjustedInventory1. The following is a reference DAX expression:

EndingInventory =
VAR InvoiceQty =
    SUM ( 'InvoiceVolumeReport'[Invoice Quantity] )
VAR ForecastedQty =
    SUM ( 'Demand'[ForecastedQty] )
VAR CurrentMonthInventory =
    SUM ( CurrentInventory[Quantity] )
VAR PackagingValue =
    SUM ( 'Packaging'[Value] )
RETURN
    IF (
        InvoiceQty > ForecastedQty,
        CurrentMonthInventory + PackagingValue,
        CurrentMonthInventory - ForecastedQty + InvoiceQty + PackagingValue
    )

I hope the above DAX can be helpful to you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[danextian]

Hi @hchauhan556 ,

This is where you problem lies.

Inventory =
VAR CurrentMonthInventory = SUM(CurrentInventory[Quantity])
VAR LastMonthInventory = CALCULATE([Ending Inventory], PREVIOUSMONTH('Date'[Date]))

RETURN
IF(CurrentMonthInventory = 0, LastMonthInventory, CurrentMonthInventory)

Your Inventory measure references Ending Inventory but Ending Inventory also references Inventory.  If you did the same thing in Excel, you'd also get a similar error.

[hchauhan556]

Hi Danextian

both measures are mandatory and dependent on each other. how can I remove circular dependency on that point?

kind regards