Forum Discussion
Closing and opening inventory calculation
Hi All,
I have been strugling with an issue and hope someone can help me with a suggestion.
Problem:
I would like to calcuate the future closing/opening inventory based on production and sales forecasts.
The current month opening inventory is available, the productions and sales forecasts are available (for the following 15 months period). From this I can calculate the current month's closing inventory by using the follwing formula : Opening inventory + production FC - sales FC
The calculated closing inventory should be the following month's opening inventory and the above calculation should be used to calculate the following month's closing inventory.
The issue is that I run into circular dependency error message, because of this closing and opening inventory measurement.
See the expected result: (the data with green backround is available in different tables, the rest should be calculated)
Many thanks for looking into this!!
If you keep the Opening Inventory white cells in a separate column, this should be a simple task of running totals where you calculate:
[Open Green Column Value] + [Production FC] - [Sales FC]
and use variables or time intelligence to calculate the running total of that expression.
Do similar with the closing balance, just adjust the filters slightly.
Do you have a DimDate table?
6 Replies
- AllisonKennedy
Community Champion
If you keep the Opening Inventory white cells in a separate column, this should be a simple task of running totals where you calculate:
[Open Green Column Value] + [Production FC] - [Sales FC]
and use variables or time intelligence to calculate the running total of that expression.
Do similar with the closing balance, just adjust the filters slightly.
Do you have a DimDate table?
Hi AllisonKennedy, yes I have a DimDate and the Year Months values are taken from that table. Why is this important?
- AllisonKennedy
V1 Just if you need help writing the running total measure it's necessary to know what the data model looks like. Are you comfortable writing running totals, or do you want more guidance on that?
