inventory aging buckets with FIFO

[daowei]

Dear all,

I have been struggling with a caculation for a few days, but cannot find a way to get around the circular dependency error.  

Purchase	Sale	Age	Corrected Stock at the begining of each week  (Inc. Discard)
		1w	2w	3w	4w	5w	6w (Discard)
2000	250	2000	0	0	0	0	0	2000
	550	0	1750	0	0	0	0	1750
	500	0	0	1200	0	0	0	1200
1750	250	1750	0	0	700	0	0	2450
	250	0	1750	0	0	450	0	2200
	775	0	0	1750	0	0	200	1950
1750	350	1750	0	0	975	0	0	2725
3500	75	3500	1750	0	0	625	0	5875
	900	0	3500	1750	0	0	550	5800
1500	450	1500	0	3500	850	0	0	5850
	1325	0	1500	0	3500	400	0	5400
3000	150	3000	0	1500	0	2575	0	7075
2750	625	2750	3000	0	1500	0	2425	9675
5000	1175	5000	2750	3000	0	875	0	11625
3500	2825	3500	5000	2750	2700	0	0	13950

Here is a example of aging buckets I created in Excel, based on four assumptions

1. Before simulation we have the values in first row, start with 2000 in 1 week aging bucket.

2. The aging bucket display number of stock at the begining of each week. 

3. First in first out

4. Any item older than 6 weeks will be discarded, therefore in the next week unused item in week 6 will be zero. 

In Excel it's easy to caculate remaining units from last week for deduction, but in PowerBI caculated column I keep getting circular dependency error, as I try to sumx previous values. Any suggestions on how to solve this problem?

[bhanu_gautam]

@daowei To avoid circular dependency errors in Power BI when calculating inventory aging buckets with FIFO, you can use measures instead of calculated columns.

Ensure you have a table with your purchase and sale data, including the week number.

Calculate the total purchases for each week.

     Total Purchases = SUM('Inventory'[Purchase])

Calculate the total sales for each week.

Total Sales = SUM('Inventory'[Sale])

DAX
Stock at Beginning of Week =
VAR CurrentWeek = MAX('Inventory'[Week])
VAR PreviousWeekStock =
CALCULATE(
[Stock at End of Week],
FILTER(
'Inventory',
'Inventory'[Week] = CurrentWeek - 1
)
)
RETURN
IF(
ISBLANK(PreviousWeekStock),
[Total Purchases],
PreviousWeekStock
)

Stock at End of Week =
[Stock at Beginning of Week] + [Total Purchases] - [Total Sales]

Stock 1 Week =
CALCULATE(
[Stock at End of Week],
FILTER(
'Inventory',
'Inventory'[Week] = MAX('Inventory'[Week]) - 1
)
)

Discarded Stock =
CALCULATE(
[Stock at End of Week],
FILTER(
'Inventory',
'Inventory'[Week] <= MAX('Inventory'[Week]) - 6
)
)

[daowei]

Hello bhanu_gautam,

I've tried to create measures with your methods, but Powerbi report a circular dependency between [stock at end of week] and [stock at beginning of week] again... 

[bhanu_gautam]

@daowei , 

Stock at Beginning of Week =
VAR CurrentWeek = MAX('Inventory'[Week])
VAR PreviousWeekStock =
CALCULATE(
SUMX(
FILTER(
'Inventory',
'Inventory'[Week] = CurrentWeek - 1
),
[Total Purchases] - [Total Sales]
)
)
RETURN
IF(
ISBLANK(PreviousWeekStock),
[Total Purchases],
PreviousWeekStock
)

and DAX
Stock at End of Week =
VAR CurrentWeek = MAX('Inventory'[Week])
RETURN
CALCULATE(
[Stock at Beginning of Week] + [Total Purchases] - [Total Sales],
FILTER(
'Inventory',
'Inventory'[Week] = CurrentWeek
)
)

[daowei]

Hello bhanu_gautam, thank you for your reply, but the result is still a bit far from my expectation.

Maybe my previous description was not so clear. Here is the final table I'd like to get The remaining Purchase amount from current week goes to AGE WEEK1 bucket, that's easy. Then in next week, all remaining amount from AGE Week 1 goes to AGE WEEK 2 bucket etc.

If a bucket has less than the remaining amount for sale for that week, then 0.

The consumption starts from the oldest AGE bucket (week 6), if amount insufficent, then move to age bucket week 5 etc. 

So how do I offset previous remaining amount into next age bucket and 'loop' this for six times.