Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

PowerPivot Circular Reference Help

Can someone please help with the attached. I am trying to calculate the Production and Closing inventry based on demand, safety stock and opening stock. The production & opening calculation is dependent on the closing inventory of the previous period.  I get into a circular reference issue in power pivot. 

 

Production = Demand + Safety Stock - Opening Inventory

Closing = opening + Production - Demand

Opening = closing of previous period except for 1st period.

 

Output expected:

Example File 

 

Appreciate if anyone can help.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You need to create measure like

     

    Inventory / OnHand =
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date]))) // [Intial Inventory]  is measure

    Inventory / OnHand =
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    for opening

    Inventory / OnHand =
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <Minx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <Minx(date,date[date])))

     

    use date from date table in visual and slicer

     


    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    To the best of my knowledge, this is circular logic even to a layperson (not an MS Excel user).  Production depends up on the closing stock of the previuos month and closing stock of the previous month depends up on the production in that month.  Even if you try to create formulas for this in an MS Excel file, how you will solve this.  What will those formuals be?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello,

       

      Implementing in excel is straightforward. The production and closing calculations for the first period is different from the remaining periods.

       

      First Period Production Calculations

       

      Second Period and onwards calculation

       

      First period closing calculation

      Second Period and onward closing calculations

       

      Final overall desired output (production and closing and calculated data, rest are input data)

      implementing this in excel is easy, but i am trying for a PBI solution as there are 100's of SKUs that I need to apply it to.

       

      Thanks 

      OD