cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamalmsharma Frequent Visitor
Frequent 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...


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

Proud to be a Datanaut!


kamalmsharma Frequent Visitor
Frequent 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

 

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