I am trying to forecast inventory levels based on assumed sales (4-month average sales as at the end of March) and purchases that will be arriving.
All amounts are measured in KG’s.
I am struggling with the DAX formula to get my closing balance of my inventory to work out correctly.
The opening balance is equal to the closing balance of the previous month (except for April - this is the actual inventory level at the beginning of the month).
I need the closing balance to be worked out as follows: opening balance + stock change). When I try this, I get an error saying there is a circular dependency. I have therefore had to use another column just to get the visual to show.
It seems you’re encountering a common issue with DAX when trying to calculate a running total or closing balance due to circular dependency. Here’s a solution to calculate the closing balance of your inventory correctly.
Create a Calculated Column for the Opening Balance:
This column will take the closing balance of the previous month as the opening balance for the current month.
Adjust the Calculation to Avoid Circular Dependency:
If the above doesn’t resolve the circular dependency, consider using variables and separate calculations to avoid direct dependencies within the same context.
Here’s a more robust example incorporating variables:
In case these steps do not fully resolve your issue, I recommend uploading your query and model details on the Data Mentor platform for more detailed analysis.