cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamalmsharma Regular Visitor
Regular Visitor

How to progressively subtract week wise sales forecast from inventory to create inventory forecast?

Hi,

 

I am trying to create a weekly inventory forecast by subtracting weekly sales forecast from the opening inventory. I cannot create a column for this as the weekly sales forecast is dynamically calculated using a measure.

 

I have created a matrix with weeks in columns field and products in rows field. The problem is that the matrix visual is considering opening inventory of each week equal to the opening inventory of the first week. It should consider closing inventory of previous week as opening inventory of current week.

 

Your help will be highly useful.

 

Best regards,

Kamal

3 REPLIES 3
Super User
Super User

Re: How to progressively subtract week wise sales forecast from inventory to create inventory foreca

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kamalmsharma Regular Visitor
Regular Visitor

Re: How to progressively subtract week wise sales forecast from inventory to create inventory foreca

I am trying to explain the problem here.

 

1. I have created a measure to calculate the weekly sales forecast. This is dynamic as I need to change the reference historical week which changes the forecast. The matrix visual for the sales forecast looks like:

 

Sales Forecast  
ProductWeek 1Week 2Week 3
A203025
B10020
C151015
D201520
E251520

 

2. I have an inventory table which gives me opening inventory for Week 1. Now I want to calculate closing inventory for future weeks as: Closing Inventory = Opening Inventory - Sales Forecast. 

 

I am facing a problem of Opening Inventory not being referred to previous week's closing inventory in matrix visual. It always refers to opening inventory of week 1. 

 

Inventory Forecast        
 Week 1Week 2Week 3
ProductOpening InventorySales ForecastClosing InventoryOpening InventorySales ForecastClosing InventoryOpening InventorySales ForecastClosing Inventory
A200201802003017020025175
B18010170180018018020160
C250152352501024025015235
D300202803001528530020280
E150251251501513515020130

 

 

Could you pls advise how to take last weeks' closing inventory as current week's opening inventory?

 

Best regards,

Kamal

 

Community Support Team
Community Support Team

Re: How to progressively subtract week wise sales forecast from inventory to create inventory foreca

Hi @kamalmsharma,

 

Could you please share your sample data to me?  As by the matrix visual your shared, am not sure about what is your data struct and for the other columns. Did them come from the same table or not? You can uoload your files to one drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,849 guests
Please welcome our newest community members: