I'm looking for some help with the following inventory scenario:
The On Hand value (shown in blue) should only be used to determine the Ending Stock value for the first row.
All other rows should use the Ending Stock value (shown in red) from the previous row as the starting point for the week's Ending Stock value (shown in red).
Any help would be greatly appreciated!
Solved! Go to Solution.
Share the raw data (in a format that can be pasted in an MS Excel file) from where you want to generate the second table.
Thanks @Ashish_Mathur - here's a table showing the format of the raw data in a simplified format
(I'm having trouble posting a table here, so I've separated the fields using pipe symbol):
Week Ending | 5/07/21 | 5/14/21 | 5/21/21 | 5/28/21 | 6/04/21 | 6/11/21
Quantity on Hand | 1230.8 | 1230.8 | 1346.8 | 1346.8 | 1778.8 | 1778.8
Quantity In Production | 0 | 288 | 0 | 432 | 0 | 0
Quantity on Sales Orders | 255 | 255 | 255 | 255 | 255 | 255
Thanks so much Ashish! This worked out perfectly.
Quick follow-up question - I've used a Calendar Table developed in Power Query, that is not based on the min date of the dataset, rather the start of the year. Would there be a way to make the formulas work with that Calendar Table?
You are welcome. I will have to tweak my formulas for that. For now, please adjust your Calendar table to start from the first date of the dataset.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!