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

ahie123

New Member
Joined
Nov 15, 2015
Messages
2
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:

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.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top