PowerPivot Circular Reference Help

[Anonymous]

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.

[Ashish_Mathur]

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]

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

[amitchandak]

@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.