cancel
Showing results for
Search instead for
Did you mean:
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

## 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!

Highlighted
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 Product Week 1 Week 2 Week 3 A 20 30 25 B 10 0 20 C 15 10 15 D 20 15 20 E 25 15 20

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 1 Week 2 Week 3 Product Opening Inventory Sales Forecast Closing Inventory Opening Inventory Sales Forecast Closing Inventory Opening Inventory Sales Forecast Closing Inventory A 200 20 180 200 30 170 200 25 175 B 180 10 170 180 0 180 180 20 160 C 250 15 235 250 10 240 250 15 235 D 300 20 280 300 15 285 300 20 280 E 150 25 125 150 15 135 150 20 130

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

Best regards,

Kamal

Community Support Team

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

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.