Calculating Target Inventory and next week's Opening Stock

asusetyo

Board Regular
Joined
Feb 18, 2014
Messages
50
Dear friend,

I was trying to calculate 2 things, Opening stock in the next week and Target Inventory in each weeks.

Opening Stock in 2014/10/w2 will be : (Opening Stock - Sell Out) from 2014/10/w1
Target Inventory in 2014/10/w1 will be : SUM of Sell Out 2014/10/w1 & 2014/10/w2 (depends on the Stock Policy, 2 means 2 weeks of Sell Out).

Is there any way to calculate this things?

WeekSKU CODESell OutOpening StockStock PolicyTarget Inventory
2014/10/w110004110202
2014/10/w210004110 2
2014/10/w310004111 2
2014/10/w410004111 2
2014/10/w510004110 2
2014/11/w110004111 2
2014/11/w210004111 2
2014/11/w310004112 2
2014/11/w410004113 2
2014/12/w110004112 2
2014/12/w210004112 2
2014/12/w310004113 2
2014/12/w410004114 2

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Thanks,
Adit
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I would think your stock measure would look something like this?

[CurrentStock] := CALCULATE(SUM(Table[OpeningStock]) - SUM(Table[SellOut]), FILTER(ALL(Calendar), Calendar[WeekId] < MAX(Calendar[WeekId])))
 
Upvote 0
I would think your stock measure would look something like this?

[CurrentStock] := CALCULATE(SUM(Table[OpeningStock]) - SUM(Table[SellOut]), FILTER(ALL(Calendar), Calendar[WeekId] < MAX(Calendar[WeekId])))

Hi Scottsen,

This is what I do, i change the week into date format, and i applied your formula like this,

[CurrentStock]=CALCULATE(SUM(Table1[Opening Stock]) - SUM(Table1[Sell Out]),FILTER(ALL(Table1),Table1[Week]<max(table1[week])))<max(table1[week])))[ code]
< MAX(Table1[Week])))
<max(table1[week])))

It gives me error.

WeekSKU CODESell OutOpening StockStock PolicyTarget Inventory
10/20/201410004110502
10/21/2014100041102
10/22/2014100041112
10/23/2014100041112
10/24/2014100041102
10/25/2014100041112
10/26/2014100041112
10/27/2014100041122
10/28/2014100041132
10/29/2014100041122
10/30/2014100041122
10/31/2014100041132
11/1/2014100041142

<tbody>
</tbody>

</max(table1[week])))
</max(table1[week])))<max(table1[week])))[>
 
Last edited:
Upvote 0
Hi Scottsen,

I for current stock, i made a new table for calendar and add a relationship in it, and it works!!

<table1[date]))
=CALCULATE(SUM(Table1[Opening Stock])-SUM(Table1[Sell Out]),FILTER(Table2,Table2[WeekId] < Table1[Date]))

but it cant be done if i have other SKU there (100041 & 100042), please advice.

<table border="0" cellpadding="0" cellspacing="0" style="width: 535px" width="535"><colgroup><col><col><col><col><col><col></colgroup><tbody></tbody></table></table1[date]))
 
Last edited by a moderator:
Upvote 0
Sorry the table messed up,

WeekSKU CODESell OutOpening StockIntransit StockStock Policy
2014/10/w1100041105002
2014/10/w210004110202
2014/10/w31000411102
2014/10/w41000411102
2014/10/w51000411002
2014/11/w1100041112
2014/11/w2100041112
2014/11/w3100041122
2014/11/w4100041132
2014/12/w1100041122
2014/12/w2100041122
2014/12/w3100041132
2014/12/w4100041142
2014/10/w11000424015002
2014/10/w2100042401702
2014/10/w31000424102
2014/10/w41000424102
2014/10/w51000424002
2014/11/w1100042412
2014/11/w2100042412
2014/11/w3100042422
2014/11/w4100042432
2014/12/w1100042422
2014/12/w2100042422
2014/12/w3100042432
2014/12/w4100042442

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Sorry, I am not clear on what your current problem is? I mean, "it doesn't work" means... gives error, gives wrong result?
 
Upvote 0
Scottsen, sorry cant explain this to you well,

I want a new Formula to calculate my opening stock, intransit stock, and minus sell out.
You can see the numbers I wish to see in Opening Stock Incl INT & Ending Stock,

On the begining week (2014/10/w1) Opening Stock Incl INT will be pure (opening stock + intransit stock).
On the next week (2014/10/w2 etc) Opening Stock Incl INT will be (Ending Stock from past week + intransit stock).
Ending Stock will be simple, (Opening Stock Incl INT - Sell Out).

Here is my data below, I have date from October till December in Week, and 2 SKU Code.
Hope this explains you well.

WeekSKU CODESell OutOpening StockIntransit StockOpening Stock Incl INTEnding Stock
2014/10/w1100041105005040
2014/10/w210004110 206050
2014/10/w310004111 05039
2014/10/w410004111 03928
2014/10/w510004110 02818
2014/11/w110004111 187
2014/11/w210004111 7-4
2014/11/w310004112 -4-16
2014/11/w410004113 -16-29
2014/12/w110004112 -29-41
2014/12/w210004112 -41-53
2014/12/w310004113 -53-66
2014/12/w410004114 -66-80
2014/10/w1100042401500150110
2014/10/w210004240 170280240
2014/10/w310004241 0240199
2014/10/w410004241 0199158
2014/10/w510004240 0158118
2014/11/w110004241 11877
2014/11/w210004241 7736
2014/11/w310004242 36-6
2014/11/w410004243 -6-49
2014/12/w110004242 -49-91
2014/12/w210004242 -91-133
2014/12/w310004243 -133-176
2014/12/w410004244 -176-220

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Thanks,
Adit
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top