cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gdbaerg
Frequent Visitor

Inventory Planning Scenario

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]

 

ONENOTE_AMHsYziWWU.jpg

 

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).   

 

ONENOTE_H9LcPJyKqX.jpg

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

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.


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

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

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? 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
daxer-almighty
Solution Sage
Solution Sage

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors