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

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
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

That worked like a charm. Thanks a lot mahoneypat.