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

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.