Forum Discussion
zeckert
Helper I
Helper INeed help calculating from a total every week until 0
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 mor...
zeckertHelper I
Helper Ithe 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
zeckertHelper I
Helper Ihere 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 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 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