Need help calculating from a total every week until 0

[zeckert]

I have an inventory for several products and a weekly run rate.  I need to set up a system that will allow me to forecast my inventory across a 52 week period.  It needs to recongnize there is no more inventory and show whatever lesser amount is.  I'd like this to be connected to a date table if possible.  It also needs to be able to filtered across all products.

EX.

RR = 2 Invetory = 10

week 1 RR = 2 Inventory = 8

product 1 RR =1 Inventory = 5

Product 2 rr = 2 Inventory = 5

week 2 RR = 2 Inventory = 6

product 1 RR =1 Inventory = 4

Product 2 rr = 2 Inventory = 4

week 3 RR = 2 Inventory = 4

week 4 RR = 2 Inventory = 2

week 5 RR = 2 Inventory = 0

week 6 RR = 0 Inventory = 0

[v-jiascu-msft]

Hi @zeckert,

Can you share the original data? A dummy one is enough.

1. What's "RR"?

2. What the result do you expect?

Best Regards,

Dale

[zeckert]

the RR means Run Rate.  So basically i have formula that will count and calculate a 30 day average that will become my run rate per product.  I have a limited inventory for each product so each week the run rate is the amount of production each product will have and then will minus from the total inventory.  The inventory does not replenish so once it hits zero its zero onward.  I'll try to get a dummy set of data.  hopefully this might help a bit more.

Product 1   RR=5

product 2   RR=3

product 3   RR=4

[zeckert]

here is a preview of the inventory that my averages will go against.

Project	Inventory	6/28/2018	7/5/2018	7/12/2018	7/19/2018	7/26/2018	8/2/2018	8/9/2018	8/16/2018	8/23/2018	8/30/2018	9/6/2018	9/13/2018
		0	0	0	0	0	0	0	0	0	0	0	0
708	16	15.75	15.5	15.25	15	14.75	14.5	14.25	14	13.75	13.5	13.25	13
710	4	3	2	1	0	0	0	0	0	0	0	0	0
713	1.5	0	0	0	0	0	0	0	0	0	0	0	0
714	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5	56.5
715	24.5	22.75	21	19.25	17.5	15.75	14	12.25	10.5	8.75	7	5.25	3.5
716	52.5	51.75	51	50.25	49.5	48.75	48	47.25	46.5	45.75	45	44.25	43.5
717	30	26	22	18	14	10	6	2	0	0	0	0	0
718	26.5	22.75	19	15.25	11.5	7.75	4	0.25	0	0	0	0	0
719	61.5	61	60.5	60	59.5	59	58.5	58	57.5	57	56.5	56	55.5
720	27	27	27	27	27	27	27	27	27	27	27	27	27
721	12	12	12	12	12	12	12	12	12	12	12	12	12
722	80.5	79	77.5	76	74.5	73	71.5	70	68.5	67	65.5	64	62.5
723	41	41	41	41	41	41	41	41	41	41	41	41	41
724	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5	201.5
725	183	183	183	183	183	183	183	183	183	183	183	183	183
726	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5	89.5
727	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5	110.5
728	43	43	43	43	43	43	43	43	43	43	43	43	43
729	83	83	83	83	83	83	83	83	83	83	83	83	83
730	63	63	63	63	63	63	63	63	63	63	63	63	63
731	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5	68.5
732	37	37	37	37	37	37	37	37	37	37	37	37	37
734	89	89	89	89	89	89	89	89	89	89	89	89	89
735	66	66	66	66	66	66	66	66	66	66	66	66	66
736	83	83	83	83	83	83	83	83	83	83	83	83	83

[zeckert]

Here is my averages that needs to reporduced in BI 

Project	Average 30 Days	6/28/2018	7/5/2018	7/12/2018	7/19/2018	7/26/2018	8/2/2018	8/9/2018	8/16/2018	8/23/2018	8/30/2018	9/6/2018	9/13/2018
	23.5	0	0	0	0	0	0	0	0	0	0	0	0
708	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25
710	1	1	1	1	1	0	0	0	0	0	0	0	0
713	2.5	1.5	0	0	0	0	0	0	0	0	0	0	0
714	0	0	0	0	0	0	0	0	0	0	0	0	0
715	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75
716	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75
717	4	4	4	4	4	4	4	4	2	0	0	0	0
718	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0.25	0	0	0	0
719	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5
720	0	0	0	0	0	0	0	0	0	0	0	0	0
721	0	0	0	0	0	0	0	0	0	0	0	0	0
722	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5
723	0	0	0	0	0	0	0	0	0	0	0	0	0
724	0	0	0	0	0	0	0	0	0	0	0	0	0
725	0	0	0	0	0	0	0	0	0	0	0	0	0
726	0	0	0	0	0	0	0	0	0	0	0	0	0
727	0	0	0	0	0	0	0	0	0	0	0	0	0
728	0	0	0	0	0	0	0	0	0	0	0	0	0
729	0	0	0	0	0	0	0	0	0	0	0	0	0
730	0	0	0	0	0	0	0	0	0	0	0	0	0
731	0	0	0	0	0	0	0	0	0	0	0	0	0
732	0	0	0	0	0	0	0	0	0	0	0	0	0
734	0	0	0	0	0	0	0	0	0	0	0	0	0
735	0	0	0	0	0	0	0	0	0	0	0	0	0
736	0	0	0	0	0	0	0	0	0	0	0	0	0

[zeckert]

Here is my averages i need to reproduce in BI

Project	Average 30 Days	6/28/2018	7/5/2018	7/12/2018	7/19/2018	7/26/2018	8/2/2018	8/9/2018	8/16/2018	8/23/2018	8/30/2018	9/6/2018	9/13/2018
	23.5	0	0	0	0	0	0	0	0	0	0	0	0
708	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25
710	1	1	1	1	1	0	0	0	0	0	0	0	0
713	2.5	1.5	0	0	0	0	0	0	0	0	0	0	0
714	0	0	0	0	0	0	0	0	0	0	0	0	0
715	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75
716	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75
717	4	4	4	4	4	4	4	4	2	0	0	0	0
718	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0.25	0	0	0	0
719	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5
720	0	0	0	0	0	0	0	0	0	0	0	0	0
721	0	0	0	0	0	0	0	0	0	0	0	0	0
722	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5
723	0	0	0	0	0	0	0	0	0	0	0	0	0
724	0	0	0	0	0	0	0	0	0	0	0	0	0
725	0	0	0	0	0	0	0	0	0	0	0	0	0
726	0	0	0	0	0	0	0	0	0	0	0	0	0
727	0	0	0	0	0	0	0	0	0	0	0	0	0
728	0	0	0	0	0	0	0	0	0	0	0	0	0
729	0	0	0	0	0	0	0	0	0	0	0	0	0
730	0	0	0	0	0	0	0	0	0	0	0	0	0
731	0	0	0	0	0	0	0	0	0	0	0	0	0
732	0	0	0	0	0	0	0	0	0	0	0	0	0
734	0	0	0	0	0	0	0	0	0	0	0	0	0
735	0	0	0	0	0	0	0	0	0	0	0	0	0
736	0	0	0	0	0	0	0	0	0	0	0	0	0

[zeckert]

Here is a Preview of the Run rate table i would like to reproduce in BI with minimal formulas

Project	Average 30 Days	6/28/2018	7/5/2018	7/12/2018	7/19/2018	7/26/2018	8/2/2018	8/9/2018	8/16/2018	8/23/2018	8/30/2018	9/6/2018	9/13/2018
	23.5	0	0	0	0	0	0	0	0	0	0	0	0
708	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25
710	1	1	1	1	1	0	0	0	0	0	0	0	0
713	2.5	1.5	0	0	0	0	0	0	0	0	0	0	0
714	0	0	0	0	0	0	0	0	0	0	0	0	0
715	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75	1.75
716	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75
717	4	4	4	4	4	4	4	4	2	0	0	0	0
718	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0.25	0	0	0	0
719	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5
720	0	0	0	0	0	0	0	0	0	0	0	0	0
721	0	0	0	0	0	0	0	0	0	0	0	0	0
722	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5	1.5
723	0	0	0	0	0	0	0	0	0	0	0	0	0
724	0	0	0	0	0	0	0	0	0	0	0	0	0
725	0	0	0	0	0	0	0	0	0	0	0	0	0
726	0	0	0	0	0	0	0	0	0	0	0	0	0
727	0	0	0	0	0	0	0	0	0	0	0	0	0
728	0	0	0	0	0	0	0	0	0	0	0	0	0
729	0	0	0	0	0	0	0	0	0	0	0	0	0
730	0	0	0	0	0	0	0	0	0	0	0	0	0
731	0	0	0	0	0	0	0	0	0	0	0	0	0
732	0	0	0	0	0	0	0	0	0	0	0	0	0
734	0	0	0	0	0	0	0	0	0	0	0	0	0
735	0	0	0	0	0	0	0	0	0	0	0	0	0
736	0	0	0	0	0	0	0	0	0	0	0	0	0