How to use previous day value in a measure without getting circular dependency error

[AliPoTD]

Hello,

I am experiencing a circular dependency error in my measure but I am unsure how to get around it. Can anyone please help?

I have to display a table of data showing the number of product units that are produced each day.

As ovens need to run at max capacity, there will always be some additional product at the end of the day [Closing Stock]

This is turn becomes the following day's [Opening Stock]

The [Required No. of Pots] is then reduced by the [Opening Stock] value to make the [Adjusted No.of Pots] column. 

The [Adjusted No. of Pots] column is divided by the [Oven capacity] rounded up to the nearest whol number to make the [No. of Ovens]

The [No. of Ovens] is multiplied by the [Oven capacity] to get the [Total No. of Pots]

The [Closing Stock] is created by [Opening Stock] + [Total No. of Pots] - [Required No. of Pots] 

I realise I have a looping scenario here hence the Circular Dependency references, but I don't know how to get around this.

An example test file is available on WeTransfer at: https://we.tl/t-tWSidwSNBL

Can anyone provided a solution if possible please?

I'm aiming for the below:

Many thanks in advance for any assistance provided.

[AliPoTD · Accepted solution]

Thanks everyone for your assistance but in my situation it doesn't appear to be possible. 

[v-hashadapu]

Hi @AliPoTD , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[danextian]

Closing Stock = [Total No. of Pots] - [Required No. of Pots]
/* This should be the Opening Stock value plus the total number of pots produced on the day minus the Required No of Pots. */

What is number of pots produced by the day? how do you compute it?

[AliPoTD]

This is the [Total No. of Pots], which is calculated by multiplying the [No. of Ovens] by the [Oven capacity].

However, the Number of Ovens is based on the Adjusted No. of Pots value, which is the Required No. of Pots value minus yesterday's Closing Stock

[amitchandak]

@AliPoTD , In DAX we can not do recursive calculation, it means BOP/EOP , beginning and closing stock has to build cumulative 

example 

Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))

or

Inventory / OnHand =

CALCULATE(SUM(Table[Quantity]),filter(all(date),date[date] <min(date[date])), filter(Table, Table[MovementType] in {"Purchased", "Internal Conversion"}) -
CALCULATE(SUM(Table[Quantity]),filter(all(date),date[date] <min(date[date])), filter(Table, Table[MovementType] in {"Sales"})

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[Kedar_Pande]

Use this pattern:

text
Daily Units =
CALCULATE(
SUM('Production'[Units]),
ALLEXCEPT('Production', 'Production'[Date], 'Production'[Product])
)

If still circular:

Check for bidirectional relationships → set to single direction

Replace circular measures with calculated columns where possible

Use REMOVEFILTERS() instead of ALL() on specific tables only

@AliPoTD 

[Zanqueta]

Hi @AliPoTD ,

Why the Circular Dependency Occurs
You are trying to calculate something like:
Adjusted Requirement = Required Pots - Previous Day Closing Stock
But if Closing Stock itself depends on Adjusted Requirement, you create a loop. DAX does not allow iterative row-by-row calculations in measures.

Recommended Approache:

Opening Stock =
CALCULATE(
MAX('Production'[Closing Stock]),
OFFSET(-1, ORDERBY('Production'[Date], ASC))
)

Or, if you have a proper Date table:

Opening Stock =
CALCULATE(
MAX('Production'[Closing Stock]),
PREVIOUSDAY('Date'[Date])
)

Then compute:

Adjusted Requirement =
SUM('Production'[Required Pots]) - [Opening Stock])

Official reference:

PREVIOUSDAY function (DAX) - DAX | Microsoft Learn

If this response was helpful in any way, I’d gladly accept a ‌‌much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop ‌‌.

[AliPoTD]

Thank you for this, however I still get the circular dependency error as the Adjusted Requirement is subsequently used in the calcuations for the Number of Ovens -> Total No of Pots -> Closing stock -> Opening Stock -> Adjusted Requirement and so on.

I've added a link in the original post to a test pbi file which demonstrates what I'm trying to achieve, hopefully this explains it fully.

Thanks in advance.

[rohit1991]

Hii @AliPoTD 

You must break the dependency by calculating the previous day’s value from the base data, not from the measure itself.

In Power BI the correct pattern is:

Prev Day Value :=
CALCULATE(
    [Your Base Measure],                
    DATEADD('Date'[Date], -1, DAY)
)

Then your final measure becomes:

Final Measure :=
VAR OpeningStock = [Prev Day Value]
VAR RequiredToday = [Base Requirement]
RETURN
    RequiredToday - OpeningStock

By separating the base logic into [Base Requirement] and using DATEADD() over the date table, you avoid recursion and the circular dependency disappears.