Help with circular reference error

[Anonymous]

Hi everyone-

I am new to Power BI and hoping that someone would be able to help me with a Power BI desktop column calculation.

Here is a sample of the data table I am working with:

Item	Month	Supply	Demand	On Hand Inventory (as on Jan 1)	Starting inventory	Ending inventory
A	1/1/2019	16,727	7,396	32,944	32,944	42,275
A	2/1/2019	0	8,700
A	3/1/2019	0	10,300
A	4/1/2019	37,000	10,140
A	5/1/2019	18,500	11,220
A	6/1/2019	0	9,940
A	7/1/2019	37,000	8,380
A	8/1/2019	0	16,936
A	9/1/2019	0	7,100
A	10/1/2019	0	9,560
A	11/1/2019	0	12,971

I need to calculate starting and ending inventories in each month for an item.

For the first month, the Ending inventory calculation is straight forward : On Hand inventory + Supply - Demand but for the subsequent months, the ending inventory of prior month will become starting inventory of the current month and the ending inventory of that month will need to be calculated as 'ending inventory of prior month + supply in that month - demand in that month'.

I tried one approach where i calculated previous month's ending inventory by creating Row ID using RANKX, and then another column (Prior Row ID) using EARLIER to get the previous Row ID. I then used this Prior Row ID to calculate ending inventory of prior month:

rowID :

RANKX(FILTER('Analysis - Item level',

ALL('Analysis - Item level'),

'Analysis - Item level'[Item]=EARLIER('Analysis - Item level'[Item])),

'Analysis - Item level'[Month],,ASC)

PreviousRowID:

CALCULATE(MAX('Analysis - Item level'[rowID]),'Analysis - Item level',

ALL('Analysis - Item level'),

'Analysis - Item level'[Item]=EARLIER('Analysis - Item level'[Item]),

'Analysis - Item level'[Month]<EARLIER('Analysis - Item level'[Month]))

Prior Ending Inventory:

CALCULATE(MAX('Analysis - Item level'[Ending Inventory]),

ALL('Analysis - Item level'),

                                    'Analysis - Item level'[Item]= EARLIER('Analysis - Item level'[Item]),

                                    'Analysis - Item level'[rowID]= EARLIER('Analysis - Item level'[PreviousRowID]),

                                    'Analysis - Item level'[Month]< EARLIER('Analysis - Item level'[Month]))

this formula works just for the month of Feb'19 because the ending inventory for the month of Jan'19 is already calculated but for the month of Mar'19 (and beyond) the calculation results in '0' because ending inventory of those months is not pre-populated in the data.

The challenge is that i need to calculate prior month's Ending inventory in each month (which will become starting inventory in that month) and use it to calculate ending inventory of that month, which inturn should be used to calculate prior month's ending inventory of next month, which works well in excel as the formulae there are based on cell values but I am getting circular reference error in Power BI as it is column based.

Thank you in advance for helping me out!

[d_gosbell]

If your data is exactly how you have it pictured below you should be able to create measures to calculate the Starting and Ending Inventory using the first 3 columns. You should be able to do this without recursion just by adding up the lifetime to date values for supply and demand. The only difference between the start and end inventory is the _priorMonths variable where start is less than the current month and end is less than or equal to the current month.

eg.

Starting Inventory = 
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] < MAX(Table1Month] ) 
var _priorSupply = CALCULATE( SUM( Table1[Supply] , _priorMonths )
var _priorDemand = CALCULATE( SUM( Table1[Demand] , _priorMonths )

RETURN SUM(Table1[ On Hand Inventory (as on Jan 1)]) + _priorSupply - _priorDemand 

Ending Inventory = 
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1Month] ) 
var _priorSupply = CALCULATE( SUM( Table1[Supply] , _priorMonths )
var _priorDemand = CALCULATE( SUM( Table1[Demand] , _priorMonths )

RETURN SUM(Table1[ On Hand Inventory (as on Jan 1)]) + _priorSupply - _priorDemand 

[Anonymous]

thanks for you quick response @d_gosbell !

Actually the data i am working with has lot more items. The inventories need to be calculated at item - month level.

Item	Month	Supply	Demand	On Hand inventory (as of Jan 1)	Starting inventory	Ending inventory
A	1/1/2019	16,727	7,396	32,944	32,944	42,275
A	2/1/2019	0	8,700
A	3/1/2019	0	10,300
A	4/1/2019	37,000	10,140
A	5/1/2019	18,500	11,220
A	6/1/2019	0	9,940
A	7/1/2019	37,000	8,380
A	8/1/2019	0	16,936
A	9/1/2019	0	7,100
A	10/1/2019	0	9,560
B	1/1/2019	6,000	12,971	152,549	152,549	139,578
B	2/1/2019	0	13,689
B	3/1/2019	0	24,886
B	4/1/2019	17,039	24,776
B	5/1/2019	0	14,055
B	6/1/2019	0	13,769
B	7/1/2019	0	23,921
B	8/1/2019	0	1,750
B	9/1/2019	0	1,750
B	10/1/2019	18,500	1,750

I tried the formula you suggested but its not giving me the desired result : the result of the formula On Hand invenotry * # records in the table as value for each row (in my earlier example, i had 10 rows and 32,944 was starting inventory for Jan. the result of the formula was 329,440 for every record)

[d_gosbell · Accepted solution]

I'm not sure how you could get #rows times the starting inventory. However there are some issues with the previous expressions I post, but I think the general approach is still the correct one.

These two measures appear to do the trick

Starting Inventory2 = 
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] < MAX(Table1[Month] )) 
var _priorAndCurrentMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) 
RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]),_priorAndCurrentMonths) + CALCULATE(SUM( Table1[Supply]) -SUM( Table1[Demand])  , _priorMonths )

Ending Inventory2 = 
var _priorMonths = FILTER( ALL(Table1[Month]), Table1[Month] <= MAX(Table1[Month] )) 
RETURN CALCULATE(SUM(Table1[On Hand inventory (as of Jan 1)]) + SUM( Table1[Supply]) -SUM( Table1[Demand])  , _priorMonths )

[Anonymous]

thank you so much @d_gosbell ! this is really helpful.

Apologies for the delayed response... since i am new to Power BI, it was not very apparent to me what the formula you suggested was evaluating but after doing some research and playing aroung with Vars, i now understand the logic.