Hi there,
I'm trying to plan for inventory demand and one of the issues with that is accounting for out of stocks. If I'm selling 25 units a week on average and one week I ran out of inventory, then I want to calculate what I should have sold if it was in stock. So instead of a normal average (75 sold/4 weeks = ~19 per week) I want it to account for that out of stock and take what the average has been prior when they were in stock (so it'd know 100 would have sold / 4 weeks = 25 a week)
I started with first creating a flag system that shows if we ran out of stock that week. I added a column to the data: if the type = inventory and the inventory is less than 3(our threshold), put a 1 and if the quantity was greater than 3, give a 0. This gave me the table below, "Days OOS".
You can see that week 8/30/2020 had 2 days where inventory was less than 3 and then it's full 7/7 days for 3 weeks that we were out of stock. Now that the flag is in place, I'm trying to make a formula that says, "if Days OOS is greater than 0, use the last 4 weeks average from that date". You can see the "math" in action in the chart below this one that I made in Excel.
The issue I'm struggling with is having PowerBI know how to look back the 4 weeks from the specific date itself. Right now, it's doing 4-week average from either the start of the date range I have filtered OR the past 4 weeks from today. I need it to reference the 4 weeks before 8/30/2020. Another issue is how to make it iterate over multiple weeks out of stock, like the example below.
Does anyone have an idea of how I should go about this? I really appreciate any help you can give!