Seeking Guidance from Power Query Experts: Assistance Needed for Stock Analysis in Excel

[ahie123]

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!

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Redirecting

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.