Stock simulation over time

[robbinkees1]

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? 

[amitchandak]

@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]

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 · Accepted solution]

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.