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 data sample is like below:

 

I need to get the table with calculated columns as below using DAX (in Power BI).


I have used these formulae to create calculated columns but causing Circular Dependency Error.

Opening Stock = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            'Table1',
            'Table1'[Date] < EARLIER ( 'Table1'[Date] )
        ),
        'Table1'[Date], DESC
    )
VAR PreviousValue =
    IF(
        MINX ( PreviousRow, 'Table1'[Closing Stock] ) = BLANK(), 
        [Purchased Stock], 
        MINX ( PreviousRow, 'Table1'[Closing Stock] )
    )
RETURN
    PreviousValue

---------------------------------

Closing Stock = IF(
    [Purchased Stock] <> BLANK(),
    [Purchased Stock] + [Opening Stock] - [Sold Stock],
    [Opening Stock] - [Sold Stock]
)

I was able to get the logic for Opening Stock column from Zubair_Muhammad. Zubair_Muhammad, it would be great if you or anyone from the community helped me obtain the solution.

 

Please help me to arrive to a solution..

Thanks in Advance.

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
     

5 Replies

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pat ( mahoneypat ), Thanks for the help recently.

       

      I have a scenario where, the data is like this...

      which is clear that the purchased stock value is not available. But can be obtained and accounted for manually with the help of inventory department (which cannot be obtained while creating the above table). and for the first row in opening stock I can enter the opening stock value manually in Excel (as in the image below) but the same thing when I did in PowerBI, the things are breaking. and getting the negative values.

       

       

      Moreover, there are categories of stock to be accounted in this data, for example there are 3 categories of stock, whose count need to be tracked on perday basis. (not mandatory that each category of stock is sold on a particular date and also not mandatory that when stock is purchased all categories of stock are purchased on a particular date).

       

      I would be very thankful if you could help me obtain solution for these. And help from any community members is verymuch invited.

       

      Thanks in Advance.

      • 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