Results 1 to 7 of 7

Thread: Calculating Target Inventory and next week's Opening Stock
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular asusetyo's Avatar
    Join Date
    Feb 2014
    Location
    Indonesia, Jakarta
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Calculating Target Inventory and next week's Opening Stock

    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?

    Week SKU CODE Sell Out Opening Stock Stock Policy Target Inventory
    2014/10/w1 100041 10 20 2
    2014/10/w2 100041 10 2
    2014/10/w3 100041 11 2
    2014/10/w4 100041 11 2
    2014/10/w5 100041 10 2
    2014/11/w1 100041 11 2
    2014/11/w2 100041 11 2
    2014/11/w3 100041 12 2
    2014/11/w4 100041 13 2
    2014/12/w1 100041 12 2
    2014/12/w2 100041 12 2
    2014/12/w3 100041 13 2
    2014/12/w4 100041 14 2
    Thanks,
    Adit

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Target Inventory and next week's Opening Stock

    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])))
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    Board Regular asusetyo's Avatar
    Join Date
    Feb 2014
    Location
    Indonesia, Jakarta
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Target Inventory and next week's Opening Stock

    Quote Originally Posted by scottsen View Post
    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])))

    It gives me error.

    Week SKU CODE Sell Out Opening Stock Stock Policy Target Inventory
    10/20/2014 100041 10 50 2
    10/21/2014 100041 10 2
    10/22/2014 100041 11 2
    10/23/2014 100041 11 2
    10/24/2014 100041 10 2
    10/25/2014 100041 11 2
    10/26/2014 100041 11 2
    10/27/2014 100041 12 2
    10/28/2014 100041 13 2
    10/29/2014 100041 12 2
    10/30/2014 100041 12 2
    10/31/2014 100041 13 2
    11/1/2014 100041 14 2

    Last edited by asusetyo; Oct 27th, 2014 at 12:04 AM. Reason: revision on formula

  4. #4
    Board Regular asusetyo's Avatar
    Join Date
    Feb 2014
    Location
    Indonesia, Jakarta
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Target Inventory and next week's Opening Stock

    Hi Scottsen,

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

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

    Last edited by Joe4; Oct 27th, 2014 at 01:58 PM. Reason: errored table attempt removed

  5. #5
    Board Regular asusetyo's Avatar
    Join Date
    Feb 2014
    Location
    Indonesia, Jakarta
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Target Inventory and next week's Opening Stock

    Sorry the table messed up,

    Week SKU CODE Sell Out Opening Stock Intransit Stock Stock Policy
    2014/10/w1 100041 10 50 0 2
    2014/10/w2 100041 10 20 2
    2014/10/w3 100041 11 0 2
    2014/10/w4 100041 11 0 2
    2014/10/w5 100041 10 0 2
    2014/11/w1 100041 11 2
    2014/11/w2 100041 11 2
    2014/11/w3 100041 12 2
    2014/11/w4 100041 13 2
    2014/12/w1 100041 12 2
    2014/12/w2 100041 12 2
    2014/12/w3 100041 13 2
    2014/12/w4 100041 14 2
    2014/10/w1 100042 40 150 0 2
    2014/10/w2 100042 40 170 2
    2014/10/w3 100042 41 0 2
    2014/10/w4 100042 41 0 2
    2014/10/w5 100042 40 0 2
    2014/11/w1 100042 41 2
    2014/11/w2 100042 41 2
    2014/11/w3 100042 42 2
    2014/11/w4 100042 43 2
    2014/12/w1 100042 42 2
    2014/12/w2 100042 42 2
    2014/12/w3 100042 43 2
    2014/12/w4 100042 44 2

  6. #6
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Target Inventory and next week's Opening Stock

    Sorry, I am not clear on what your current problem is? I mean, "it doesn't work" means... gives error, gives wrong result?
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  7. #7
    Board Regular asusetyo's Avatar
    Join Date
    Feb 2014
    Location
    Indonesia, Jakarta
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Calculating Target Inventory and next week's Opening Stock

    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.

    Week SKU CODE Sell Out Opening Stock Intransit Stock Opening Stock Incl INT Ending Stock
    2014/10/w1 100041 10 50 0 50 40
    2014/10/w2 100041 10 20 60 50
    2014/10/w3 100041 11 0 50 39
    2014/10/w4 100041 11 0 39 28
    2014/10/w5 100041 10 0 28 18
    2014/11/w1 100041 11 18 7
    2014/11/w2 100041 11 7 -4
    2014/11/w3 100041 12 -4 -16
    2014/11/w4 100041 13 -16 -29
    2014/12/w1 100041 12 -29 -41
    2014/12/w2 100041 12 -41 -53
    2014/12/w3 100041 13 -53 -66
    2014/12/w4 100041 14 -66 -80
    2014/10/w1 100042 40 150 0 150 110
    2014/10/w2 100042 40 170 280 240
    2014/10/w3 100042 41 0 240 199
    2014/10/w4 100042 41 0 199 158
    2014/10/w5 100042 40 0 158 118
    2014/11/w1 100042 41 118 77
    2014/11/w2 100042 41 77 36
    2014/11/w3 100042 42 36 -6
    2014/11/w4 100042 43 -6 -49
    2014/12/w1 100042 42 -49 -91
    2014/12/w2 100042 42 -91 -133
    2014/12/w3 100042 43 -133 -176
    2014/12/w4 100042 44 -176 -220


    Thanks,
    Adit

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •