Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
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 |
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 |
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 |
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
|
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |