Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Circular dependency error while creating two inter dependent columns from other columns

Hi to all the members of enthusiastic and supportive community.   I have to create two columns "closing stock" and "opening stock" columns based on "date", "purchased stock", and "sold stock". The...
Calculated columns
Circular dependency
DAX
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Please try these column expressions instead, replacing StockSales with your actual table name.

     

    Opening Stock = var vThisDate = StockSales[Date]
    var vPurchases = CALCULATE(SUM(StockSales[Purchased Stock]), All(StockSales), StockSales[Date] <= vThisDate)
    var vSales = CALCULATE(SUM(StockSales[Sold Stock]), all(StockSales), StockSales[Date] < vThisDate)
    return vPurchases - vSales
     
    Closing Stock = var vThisDate = StockSales[Date]
    var vPurchases = CALCULATE(SUM(StockSales[Purchased Stock]), all(StockSales), StockSales[Date] <= vThisDate)
    var vSales = CALCULATE(SUM(StockSales[Sold Stock]), All(StockSales), StockSales[Date] <= vThisDate)
    return vPurchases - vSales
     
    Pat
     
mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Would this approach work, adding in an initial value?  It is hardcoded below but it could be a dynamic expression with a value from another table you bring in.

 

Opening Stock =
VAR vInitValue = 100 // or use dynamic expression
VAR vThisDate = StockSales[Date]
VAR vPurchases =
    CALCULATE (
        SUM ( StockSales[Purchased Stock] ),
        ALL ( StockSales ),
        StockSales[Date] <= vThisDate
    )
VAR vSales =
    CALCULATE (
        SUM ( StockSales[Sold Stock] ),
        ALL ( StockSales ),
        StockSales[Date] < vThisDate
    )
RETURN
    vInitValue + vPurchases - vSales

 
Pat
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for the suggestion Pat ( mahoneypat  ).

It certainly helped me and there were a few corrections to be made at other (prior) stages in my data. Now result is as expected after dealing with those corrections and changes.