Hi there,
I'm looking for guidance from a Power Query expert to assist me in designing and implementing a measure for stock analysis in Excel. Specifically, I need help calculating Closing Stock and Opening Stock for a calendar year.
For Opening Stock, I'm trying to define a logic where the opening stock of the current week equals the closing stock of the previous week.
Additionally, I'm working on the calculation for Closing Stock, where the closing stock of the current week should be equal to the opening stock of the current week minus Sales, plus Intake, and minus Markdowns.
I've encountered issues like circular references or blank results. I also experimented with creating a formula in a column, but that didn't yield the desired outcome.
Any insights or suggestions would be greatly appreciated. Feel free to take a look at the attached template and provide any guidance you think might be helpful.
I have a template with sample raw data in the "Data" tab, and the "Wk" tab defines the different week statuses. Despite my attempts with various formulas, Not sure how to attach the file.
Thank you in advance for your assistance!
I'm looking for guidance from a Power Query expert to assist me in designing and implementing a measure for stock analysis in Excel. Specifically, I need help calculating Closing Stock and Opening Stock for a calendar year.
For Opening Stock, I'm trying to define a logic where the opening stock of the current week equals the closing stock of the previous week.
Additionally, I'm working on the calculation for Closing Stock, where the closing stock of the current week should be equal to the opening stock of the current week minus Sales, plus Intake, and minus Markdowns.
I've encountered issues like circular references or blank results. I also experimented with creating a formula in a column, but that didn't yield the desired outcome.
Any insights or suggestions would be greatly appreciated. Feel free to take a look at the attached template and provide any guidance you think might be helpful.
I have a template with sample raw data in the "Data" tab, and the "Wk" tab defines the different week statuses. Despite my attempts with various formulas, Not sure how to attach the file.
Thank you in advance for your assistance!