out of stock inventory by date and # of days out of stock

[wmeyer]

Our purchasing team's goal is to have an item go back in stock within 5 days of going out of stock. I picture having a chart similar to this to show them: 

Item            Out of Stock Date    Restock Date    # Business Days OOS

1519-DSR    3/21/17                    5/1/17                 29

                     3/8/18                      3/9/18               1     

I have a data table containing a line for every item, every day, along with columns to indicate whether an item was out of stock or in stock that day. OOS is what i have used to count the total # items in/out stock for a certain time period. OOS=1, IS=0. I've created columns for OOS Date and IS Date which look like: OOS Date = IF('Item by Day '[OOS]="1", '[qty_on_hand_date]).

I also created a column called "Last IS Date before this" which uses the formula: Last IS Date before this = SUMMARIZE(
TOPN(1,
FILTER(ALL('Item by Day '),
'Item by Day '[Yes/NoNEW]="0" &&
'Item by Day'[name]=EARLIER('Item by Day '[name])
&& 'Item by Day '[qty_on_hand_date]<EARLIER('Item by Day'[qty_on_hand_date])),
'Item by Day '[qty_on_hand_date]),
'Item by Day'[qty_on_hand_date]).

The goal of that formula was to determine the last in stock date before the item went OOS, so i could calculate the difference in days. 

I was thinking i would need to add the last 3 columns on the right to get the outcome i mentioned, but not sure how to do that or if it's necessary. What i have in there now is what i would like the outcome to look like.  Here is some of my data:

name	qty_on_hand_date	Yes/No NEW	OOS	OOS Date	IS Date	Last IS Date before this	Out of Stock Date	Restock Date	# Business Days OOS
1519-DSR	3/20/2017	No	0		3/20/2017 0:00	3/17/2017 0:00
1519-DSR	3/21/2017	Yes	1	3/21/2017 0:00		3/20/2017 0:00	3/21/2017	5/1/2017	29
1519-DSR	3/22/2017	Yes	1	3/22/2017 0:00		3/20/2017 0:00
1519-DSR	3/23/2017	Yes	1	3/23/2017 0:00		3/20/2017 0:00
1519-DSR	3/24/2017	Yes	1	3/24/2017 0:00		3/20/2017 0:00
1519-DSR	3/25/2017	Null				3/20/2017 0:00
1519-DSR	3/26/2017	Null				3/20/2017 0:00
1519-DSR	3/27/2017	Yes	1	3/27/2017 0:00		3/20/2017 0:00
1519-DSR	3/28/2017	Yes	1	3/28/2017 0:00		3/20/2017 0:00
1519-DSR	3/29/2017	Yes	1	3/29/2017 0:00		3/20/2017 0:00
1519-DSR	3/30/2017	Yes	1	3/30/2017 0:00		3/20/2017 0:00
1519-DSR	3/31/2017	Yes	1	3/31/2017 0:00		3/20/2017 0:00
1519-DSR	4/1/2017	Null				3/20/2017 0:00
1519-DSR	4/2/2017	Null				3/20/2017 0:00
1519-DSR	4/3/2017	Yes	1	4/3/2017 0:00		3/20/2017 0:00
1519-DSR	4/4/2017	Yes	1	4/4/2017 0:00		3/20/2017 0:00
1519-DSR	4/5/2017	Yes	1	4/5/2017 0:00		3/20/2017 0:00
1519-DSR	4/6/2017	Yes	1	4/6/2017 0:00		3/20/2017 0:00
1519-DSR	4/7/2017	Yes	1	4/7/2017 0:00		3/20/2017 0:00
1519-DSR	4/8/2017	Null				3/20/2017 0:00
1519-DSR	4/9/2017	Null				3/20/2017 0:00
1519-DSR	4/10/2017	Yes	1	4/10/2017 0:00		3/20/2017 0:00
1519-DSR	4/11/2017	Yes	1	4/11/2017 0:00		3/20/2017 0:00
1519-DSR	4/12/2017	Yes	1	4/12/2017 0:00		3/20/2017 0:00
1519-DSR	4/13/2017	Yes	1	4/13/2017 0:00		3/20/2017 0:00
1519-DSR	4/14/2017	Yes	1	4/14/2017 0:00		3/20/2017 0:00
1519-DSR	4/15/2017	Null				3/20/2017 0:00
1519-DSR	4/16/2017	Null				3/20/2017 0:00
1519-DSR	4/17/2017	Yes	1	4/17/2017 0:00		3/20/2017 0:00
1519-DSR	4/18/2017	Yes	1	4/18/2017 0:00		3/20/2017 0:00
1519-DSR	4/19/2017	Yes	1	4/19/2017 0:00		3/20/2017 0:00
1519-DSR	4/20/2017	Yes	1	4/20/2017 0:00		3/20/2017 0:00
1519-DSR	4/21/2017	Yes	1	4/21/2017 0:00		3/20/2017 0:00
1519-DSR	4/22/2017	Null				3/20/2017 0:00
1519-DSR	4/23/2017	Null				3/20/2017 0:00
1519-DSR	4/24/2017	Yes	1	4/24/2017 0:00		3/20/2017 0:00
1519-DSR	4/25/2017	Yes	1	4/25/2017 0:00		3/20/2017 0:00
1519-DSR	4/26/2017	Yes	1	4/26/2017 0:00		3/20/2017 0:00
1519-DSR	4/27/2017	Yes	1	4/27/2017 0:00		3/20/2017 0:00
1519-DSR	4/28/2017	Yes	1	4/28/2017 0:00		3/20/2017 0:00
1519-DSR	4/29/2017	Null				3/20/2017 0:00
1519-DSR	4/30/2017	Null				3/20/2017 0:00
1519-DSR	5/1/2017	No	0		5/1/2017 0:00	3/20/2017 0:00
1519-DSR	2/16/2018	No	0		2/16/2018 0:00	2/15/2018 0:00
1519-DSR	3/5/2018	No	0		3/5/2018 0:00	3/2/2018 0:00
1519-DSR	3/6/2018	No	0		3/6/2018 0:00	3/5/2018 0:00
1519-DSR	3/7/2018	No	0		3/7/2018 0:00	3/6/2018 0:00
1519-DSR	3/8/2018	Yes	1	3/8/2018 0:00		3/7/2018 0:00	3/8/2018	3/9/2018	1
1519-DSR	3/9/2018	No	0		3/9/2018 0:00	3/7/2018 0:00

[v-frfei-msft]

Hi @wmeyer,

Could you please tell me what is the logic of the last three colusmn? And kindly share the full sample data to me, you can upload the file to one drive and share the link here.

Reagrds,

Frank

[wmeyer]

Hey Frank,

I don't have logic for the last 3 columns. I added those in excel to show what I was trying to achieve there. I'm not sure if those columns are necessary or if they are the answer for helping me achieve the following chart. Maybe measures would be better for this? Any suggestions you would have for how to achieve this is appreciated!

Desired Outcome:            Item            Out of Stock Date    Restock Date    # Business Days OOS

                                        1519-DSR             3/21/17                    5/1/17                 29

                                                                      3/8/18                      3/9/18                 1     

Here is a link where you can download my sample file:

https://app.powerbi.com/view?r=eyJrIjoiMTE1ODZmYmItNGY3NS00YTFkLWE5MzQtOTczYjcxMGI0NWNkIiwidCI6IjcyM...

[Ashish_Mathur]

Hi,

Are Out of Stock and Re-stock aready there in your source dataset?  Share your data such that it can be pasted into an Excel file.  Ensure yo have the Out of Stock and Re-stock columns there.

[wmeyer]

Ashish, No the Out of Stock Date and Restock Date columns are not in my data set. Like I mentioned, those are what i need help calculating. You should be able to copy and paste the table i pasted previously. I also pasted a link to a sample file you can download. Please let me know if you need anything else to help me calculate these columns. 

[v-frfei-msft]

Hi @wmeyer,

Please check the following steps.

1. Create a date table and create 2 calculated columns in it.

date = CALENDARAUTO()

weekday = WEEKDAY('date'[Date])

Column = IF('date'[weekday]=1 || 'date'[weekday]=7,0,1)

2. Create a calculated column in the fact table.

# Business Days OOS = CALCULATE(SUM('date'[Column]),FILTER('date','Item by Day'[Out of Stock Date]<='date'[Date] && 'date'[Date]<'Item by Day'[Restock Date]))

3. Then we can get the result as we need. For more details, please check the pbix as attached.

Regards,

Frank

[wmeyer]

Hey Frank,

Thank you for your response, the table you have in your file is exactly what i want my end result to look like, but it is not working correctly because like I mentioned, i do not have the Out of stock date, or restock date columns calculated yet. 

Were you able to download the file i attached previously? You will see a line for each item each day. What i had copy and pasted above is what i want my end result of the Out of Stock date, restock date and # businesss days OOS to look like. I need help with a formula for the Out of stock date and restock date columns. I dont want an out of stock/restock date on each line, I only want the dates to pull on the first date the item was out of stock, for each period of time it went out of stock.