I am creating a PowerPivot report for supplies consumption, which contains:
and the measure is:
[Projected on Hand] = If([Actual Demand]>0, [Beginning Inventory] + [Plan Receipt]
- [Actual Demand], [Plan Receipt] - [Forecasted Demand] + [Prior Period Projected on Hand])
The problem is getting the [Prior Period Projected on Hand], which gives me a circular reference when used on Projected on Hand, since basically it's the same calculation. How do I get this to work?
The period used is weekly on the columns.
Without seeing the formula behind [Prior Period Projected on Hand] or some sample data, it is hard to know the exact fix but you can often eliminate a circular dependency by duplicating a measure and using a different name or duplicating its result as a
calculated column and then referencing the new measure/calculated column.
It really depends how your data is set up and how all measures involved are written. Circular dependencies can sometimes be tricky to troubleshoot and the error message often isn't that helpful, but I don't recall coming across one yet in my own work
that couldn't be solved with some clever duplication and/or manipulation of how the formulas are written.