Replace Weekly Units Sold with Past 4 Weeks Average Based on Out of Stock

[wsgjamesabl]

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!

[amitchandak]

@wsgjamesabl , refer if my WOW block can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...