Calculating a stock depletion

[Woody]

Hi there,

I need your help for, at least in excel, a simple formula, but in PBI I'm going crazy thinking about a solution. I have a fixed stock of an item today, let's say 42. And now I have a forecast for the next 10 days, each day a different number.

Now I want the future stock of that item, subtracted by the needed value of that day which is the new base for the next row. My goal is, getting a matrix, where I can see, on which date the stock is going to be negative so we know we need to do a new order.

My base data looks like this:

Artikel_ID	Date_of_Delivery	number of items	total_stock
1234	05.05.2020	5	10
1234	06.05.2020	4	10
1234	07.05.2020	3	10

The total stock is from right now.

What I need is a new column, which displays the subtraction until it's negative, something like this (last column), so I can create this matrix with the date as columns, the articles in the rows and the "difference" as the value part, so I have a forecast, based on our current stock of this article.

article_ID	Date_of_Delivery	number of items	total_stock	Difference
1234	05.05.2020	5	10	5
1234	06.05.2020	4	10	1
1234	07.05.2020	3	10	-2

Goal:

article_ID	05.05.2020	06.05.2020	07.05.2020	08.05.2020
1234	5	1	-2	-8
32152	50	34	10	5
43512	30	12	8	-10

Greetings

[camargos88 · Accepted solution]

Hi @Woody ,

Try this measure:

Difference =

SUM('Table'[total_stock]) - CALCULATE(SUM('Table'[number of items]); FILTER(ALL('Table'[Date_of_Delivery]); 'Table'[Date_of_Delivery] <= MAX('Table'[Date_of_Delivery])))

Ricardo

[Woody]

This works perfectly ... thank you so much

Woody

[Greg_Deckler]

Check out Days of Supply: https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318