Volume weighted Average inventory age

[Anonymous]

I have read through some previous posts but have not been able to apply the solution to my problem.  This is a long standing problem and I currently calculate the answer, manually, when someone asks; usually because there is a problem.  My goal is to provide this information on a daily basis so that inventory can be rotated properly and not just when it gets close to use by date.

Below is some of my data.  Based on the current day; I want to calculate the volume weighted average age of the inventory in each bay.  When inventory is removed, I want to calculate the volume weighted average age of the inventory removed.  I'm only showing data for 1 mill but I have multiple mills to calculate the same information.  Currently, each mill has a separate page in a spreadsheet and I pull them into Power BI as separate tables.  I would also like to know if it would be better/easier/more efficient to put all the mills into the same sheet.  

Thanks

Location	Date	Bay #	# loads placed or removed	tons placed or removed	Inventory (loads)	Inventory (tons)
Mill A	3/19/2019	2	20	584.8	20	585
Mill A	3/20/2019	2	51	1475.0	71	2,060
Mill A	3/21/2019	2	25	685.8	96	2,746
Mill A	3/25/2019	1	4	115.8	100	2,861
Mill A	3/26/2019	1	2	59.9	102	2,921
Mill A	3/27/2019	1	11	339.5	113	3,261
Mill A	3/28/2019	1	9	263.6	122	3,524
Mill A	3/29/2019	1	13	377.1	135	3,901
Mill A	4/1/2019	1	3	96.8	138	3,998
Mill A	4/2/2019	1	16	470.5	154	4,469
Mill A	4/3/2019	1	19	517.9	173	4,987
Mill A	4/4/2019	1	2	57.4	175	5,044
Mill A	4/10/2019	1	13	338.1	188	5,382
Mill A	4/12/2019	1	2	54.5	190	5,437
Mill A	4/15/2019	1	31	837.4	221	6,274
Mill A	4/16/2019	1	25	684.5	246	6,958
Mill A	4/18/2019	1	9	253.9	255	7,212
Mill A	4/23/2019	2	(23)	(437.0)	232	6,775
Mill A	4/24/2019	2	(25)	(475.0)	207	6,300
Mill A	4/25/2019	2	(21)	(399.0)	186	5,901
Mill A	5/2/2019	2	16	450.2	202	6,351
Mill A	5/3/2019	2	8	208.5	210	6,560
Mill A	5/7/2019	1	12	322.6	222	6,883
Mill A	5/8/2019	1	18	491.1	240	7,374
Mill A	5/14/2019	1	23	592.8	263	7,966
Mill A	5/15/2019	1	12	326.2	275	8,293
Mill A	5/16/2019	1	25	647.6	300	8,940
Mill A	5/17/2019	1	24	617.9	324	9,558
Mill A	5/20/2019	1	28	748.6	352	10,307
Mill A	5/21/2019	1	22	555.8	374	10,863
Mill A	5/22/2019	1	3	79.1	377	10,942
Mill A	5/22/2019	3	8	194.5	385	11,136
Mill A	5/28/2019	3	26	637.2	411	11,773
Mill A	5/29/2019	3	35	897.8	446	12,671
Mill A	5/30/2019	3	30	762.8	476	13,434
Mill A	5/31/2019	3	18	458.3	494	13,892
Mill A	6/3/2019	3	34	865.6	528	14,758
Mill A	6/4/2019	3	16	426.5	544	15,184
Mill A	6/5/2019	3	9	244.3	553	15,429
Mill A	6/6/2019	3	17	431.8	570	15,860
Mill A	6/11/2019	2	(26)	(520.0)	544	15,340
Mill A	6/12/2019	2	(7)	(140.0)	537	15,200
Mill A	6/26/2019	2	8	199.2	545	15,400
Mill A	6/27/2019	2	(27)	(486.0)	518	14,914
Mill A	6/28/2019	2	(25)	(625.0)	493	14,289
Mill A	7/1/2019	2	(11)	(198.0)	482	14,091
Mill A	7/9/2019	2	(17)	(215.4)	465	13,875
Mill A	7/10/2019	2	(7)	(108.0)	458	13,767
Mill A	10/30/2019	1	(23)	(437.0)	435	13,330
Mill A	10/31/2019	1	(21)	(525.0)	414	12,805
Mill A	11/1/2019	1	(10)	(250.0)	404	12,555
Mill A	11/4/2019	1	(22)	(550.0)	382	12,005

[v-joesh-msft]

Hi @Anonymous ,

I'm a little confused by your description: "I want to calculate the volume weighted average age of the inventory in each bay.  When inventory is removed, I want to calculate the volume weighted average age of the inventory removed. "

Could you share your calculation logic and your expected output? The calculation of the weighted average in the new quick measure in the desktop may be helpful.

About put all the mills into the same sheet, you can take a look at the video below:

Consolidate Excel Sheets with Power Query

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.