Forum Discussion

robbinkees1's avatar
robbinkees1
Frequent Visitor
4 years ago
Solved

Stock simulation over time

Hi all, 

 

I'am working on a report which tells me in which week I'm running out of stock. 

 

Stock incoming column: Purchase order quantity's that will be delivered in that week. 

Sales column: Sales order quantity's that will be send to the customer in that week. 

In / out column: Stock incoming column - sales column

 

 

Now I want in the stock over time column a simulation over the weeks when I run out of stock. (current stock - sales + incoming)

It doesn't need to fall back to the current stock every time, so for example: 

Week 20: 83 -9 = 74.

Week 21 74 - 6 = 68. 

Week 23 68 - 28 = 40.

week 24 40 + 18 =58. 

etc 

 

Is there a way how i can fix this with a measure. 

I currently try to calculate it like this, but it takes the current stock every week again and it doesn't calculate further based on the previous weeks.

 

Stock over time = SUM('crm stock'[current_stock]) + CALCULATE([In / out],FILTER(ALL('Date master2'[WeekNumber]),'Date master2'[WeekNumber]<=MAX('Date master2'[WeekNumber])))

 

Can anyone help? 

 

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, robbinkees1 

     

    You can try the following methods.

    Measure:

    Stock over time = 
SUM ( 'Table'[Current_Stock] )
    + CALCULATE (
        SUM ( 'Table'[in/out] ),
        FILTER ( ALL ( 'Table' ), [Date] <= SELECTEDVALUE ( 'Table'[Date] ) )
    )

     

    Column = 
[Current_Stock]
    + CALCULATE (
        SUM ( 'Table'[in/out] ),
        FILTER ('Table' , [Date] <= EARLIER( 'Table'[Date] ) )
    )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    robbinkees1 , We use these kinds of formulas (Measures) to build inventory. Very similar to what you have done

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    if you have date , prefer to use date not week no

    • robbinkees1's avatar
      robbinkees1
      Frequent Visitor
      4 years ago

      Hi amitchandak 

       

      Thanks for your reply! 

      But unfortunately it keeps calculating with the current stock of 83 on every day, I want to simulate it further over the time based on the incoming and outgoing stock. it doesn't need to fall back to the current. 

      Tried both measures.

       

       

      I want that it continues over time and simulating my stock levels. 

      So for example after 5/16/2022 it should calculate with 74 instead of 83. 

      The calculation on 5/23/2022 should be 74 - 6 instead of 83 - 6.

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, robbinkees1 

         

        You can try the following methods.

        Measure:

        Stock over time = 
SUM ( 'Table'[Current_Stock] )
    + CALCULATE (
        SUM ( 'Table'[in/out] ),
        FILTER ( ALL ( 'Table' ), [Date] <= SELECTEDVALUE ( 'Table'[Date] ) )
    )

         

        Column = 
[Current_Stock]
    + CALCULATE (
        SUM ( 'Table'[in/out] ),
        FILTER ('Table' , [Date] <= EARLIER( 'Table'[Date] ) )
    )

        Is this the result you expect?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.