Avoiding Circular Dependency when Calculating Inventory Projection

[stellion17]

I am soliciting forecast feedback from users for a product/location/futuredate combination and am trying to merge this feedback with an replenishment table to be able to show a monthly projected inventory balance in PowerBI. Example Formula (as would be calculated in Excel): Month 1 Figure = InitialInventory + SumInboundPOsMonth1 - ForecastMonth1. Month 2 = Month 1 Figure + SumInboundPOMonth2 - ForecastMonth2. Month 3 = Month 2 Figure + SumInboundPOMonth3 - ForecastMonth3, etc.

My issue in PowerBI is that I continue to run into circular dependency issues when referring to the forecast from the month prior in PowerBI. I've tried creating separate tables, calculated columns, measures, etc to no avail

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached files.

Hope these help.

[Anonymous]

Hi @stellion17 ,

DAX cannot reference the data in the previous row, i.e., DAX cannot implement iteration.

But based on the information you provided, perhaps you can modify your formula to:
Month 1 Figure = InitialInventory + SumInboundPOsMonth1 - ForecastMonth1
Month 2 Figure = InitialInventor + ( SumInboundPOsMonth1 + SumInboundPOMonth2 ) - ( ForecastMonth1 + ForecastMonth2 )
Month 3 Figure = InitialInventor + ( SumInboundPOsMonth1 + SumInboundPOMonth2 + SumInboundPOMonth3 ) - ( ForecastMonth1 + ForecastMonth2 + ForecastMonth3 )
...............
As long as all the data in your SumInboundPOsMonth and ForecastMonth columns are present.

Or you can use List.Accumulate or List.Generate in Power Query, two M functions that enable iteration.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.