Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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:
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.
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.
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
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.
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |