Inventory Planning Scenario

[gdbaerg]

Hi all,

I'm looking for some help with the following inventory scenario:

Current Result:

• The following table is extracted from a Power BI table. 
• The Ending Stock measure is not currently set up correctly to pull the desired result.
• Ending Stock = [On Hand]+[In Production]-[Sales]

Desired result:

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!

[Ashish_Mathur]

Hi,

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.

[gdbaerg]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[gdbaerg]

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? 

[Ashish_Mathur]

Hi,

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.

[daxer-almighty]

This should be calculated in Power Query. I hope you're not trying to do it in DAX...