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

[Anonymous]

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.

[mahoneypat · Accepted solution]

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]

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]

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]

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.

[Anonymous]

That worked like a charm. Thanks a lot @mahoneypat.