Forum Discussion

stellion17's avatar
stellion17
Regular Visitor
2 years ago
Solved

Avoiding Circular Dependency when Calculating Inventory Projection

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.