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

[kamalmsharma]

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

[Greg_Deckler]

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

[kamalmsharma]

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

[v-frfei-msft]

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