DAX circular error

Hello,

Appreciate any help in resolving a DAX circular error. I am trying to determine production quantity based on opening stock and demand

  1. The Opening stock is the same as the Closing stock of the previous period
  2. The Closing stock depends on the Opening stock and production

The formula creates a circular error. In normal excel it is easy to implement, however, i am not able to solve it in DAX.

My solution works when the initial stocks is less than the first month’s demand but breaks down with the Initial stocks exceeds 2 months’ demand.

Production:=if([TTlDemand]+[SafetyStock]>([InitialInvenotry]), [TTlDemand]+[SafetyStock]-([InitialInvenotry]),0)-[SafetyStockPreviousPeriod]

#ClosingInventory:=[InitialInvenotry]+[Production]-[TTlDemand]+[SafetyStockPreviousPeriod]

OpeningInventory:=CALCULATE([#ClosingInventory],PREVIOUSMONTH('Calendar'[Period]))


Everything is fine for product A, but product B calculations are wrong for first 2 periods
Requirement.xlsx (360.6 KB)

Hi @OscarD, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

Hi @OscarD - Please check if this Post is helpful. I had a similar issue long back and Brian helped me in resolving it.

Thanks
Ankit J

Hello,

I have attached the power bi version of it. the file was originally created in excel power pivot. its converted to power bi with an import from excel in power bi.

I have indicated the required output in the Excel file (shared earlier) and highlighted in the green highlighted section in excel calculated with standard excel formulas that i am not able to replicate with the DAX functions.

The requirement is:
Production = Demand + Safetystock - Opening inventory - Initial inventory. Production is zero or +ve. Initial inventory applies only to the first period.

Opening inventory for a period = closing inventory of the prior period.

The formulas I have created fail when the initial inventory > demand for more than 2 periods.

Regards
Oscar
Requirement.pbix (60.6 KB)
Requirement.xlsx (359.8 KB)

Hi Ankit,

Thanks for your input. This solution does not work for me. in my case the TotalSupplyQty (Production in my case) is dependent on the closing inventory of the prior period and not an input as in your example. And the closing quantity for the current period is dependent on the production of the current period. Perhaps there is something i am missing but not able to figure out… yet.

Regards
Oscar

Hi @BrianJ @Harsh - Can you help with this problem.

Thanks
Ankit J

Hi @OscarD! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!