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

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!

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi,

This should ideally be done in the DAX language i.e. PowerPivot, not in Power Query. Would you be Ok with that?

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Ashish,

Yes you are correct, my apologies I incorrectly use Power Query and Power Pivot interchangeably.

is there a way to share my template

This is what i have tried so far.

1. A calculated column but it didn't work

= VAR CurrentWeek = Data[Week]

VAR CurrentCountry = Data[Country]

RETURN

IF( CurrentWeek = 1,

Data[Opening Stock],

IF( Data[Current Week] = "Yes",

Data[Opening Stock],

CALCULATE( SUMX( FILTER( Data,

Data[Country] = CurrentCountry &&

Data[Week] <= CurrentWeek ),

Data[Intake] - Data[Sales Cost] - Data[Markdown] ),

FILTER(

ALL(Data),

Data[Week] <= CurrentWeek ) ) )

2. Created a measure to calculate the opening stock for Current Week

Osoh CurWk:=CALCULATE(

SUM(Data[Closing Stock]),

FILTER( Data, Data[Week] = MAX(Wk[Week]) && Data[Current Week] = "Prev" ) )

3. Another version of opening stock for Current Week

Cur Wk Osoh:=VAR CurrentWeek = MAX(Data[Week])

VAR CurrentCountry = MAX(Data[Country])

RETURN

IF(

MAX(Data[Current Week]) = "Yes", SUM(Data[Opening Stock]) )

4. Another version of Opening Stock

OSoh 2:=VAR CurrentWeek = MAX(Data[Week])

VAR CurrentCountry = MAX(Data[Country])

RETURN

IF(

MAX(Data[Current Week]) = "Unelapsed",

CALCULATE(

SUM(Data[Opening Stock]),

FILTER( ALL(Data), Data[Country] = CurrentCountry && Data[Week] = CurrentWeek - 1 ) ) )

5. A measure for Closing Stock

Closing Soh.:=VAR CurrentWeek = MAX(Data[Week])

VAR CurrentCountry = MAX(Data[Country])

RETURN

IF( MAX(Data[Current Week]) = "Yes", SUM(Data[Opening Stock]), IF( CurrentWeek > 1,

SUMX( FILTER( ALL(Data), Data[Country] = CurrentCountry &&

Data[Week] <= CurrentWeek ), Data[Intake] - Data[Sales Cost] - Data[Markdown] ),

BLANK() ) )

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

Share the download link of the MS Excel file and in another tab, show the expected result very clearly. Ensure that the result worksheet is formula driven so that i can translate those formulas into their DAX equivalent.

Since PowerPivot keeps crashing on my system, i will share a PBI file with you. You may study the DAX formulas in that PBI file and replicate those in your MS Excel file.

Thank you.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Ashish,

How do I share a download link?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Google Drive or similar such file hosting service.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Access denied message.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

For my solution to work, I will definitely need

  1. A proper Date column in the Data worksheet; and

  2. A Calendar Table with a week column (because that is how you want to view the result); and

  3. Proper formulas on the SUM worksheet so that i can understand them and translate those in the DAX language

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Ashish,

wow that was quick, i downloaded the file but i cannot find the measure you created

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated March 20, 2024 Views 104 Applies to: