I want to calculate date at which Stock quantity will become zero

[vrushabjain510]

I want to Calculate Date at which Stock Quantity becomes zero

The requirement is as follows:

I have a Date table created using the Min and Max Dates (Lets Say Max date in Table is 31-July-2021)

I have calculated Total Quantity available on the last date (31-July-2021) for a product X (Let this be 100 units)

I have calculated Daily average sales for last 2 months for X (Say it is 5 units i.e. 5 units of Product X are sold daily for last 2 Months)

I have incoming stock data table which says when stock is coming (Lets assume incoming stock for Product X is coming on 08-Aug-2021 of 20 Units)

So total Available Stock quantity for next Day (1-Aug-2021)will be : Total Stock Available on Last date (100)- Average Daily Sales(5) + Incoming stock quantity(0)

I want to calculate on which date will the stock quantity be zero

Expected Output should be like:            Date Available Stock Quantity 1-Aug-21 100-5+0 (0 since no incoming stock on that day) = 95 2-Aug-21 95-5 = 90 3-Aug-21 90-5 = 85 4-Aug-21 85-5 = 80 5-Aug-21 80-5 = 75 6-Aug-21 75-5 = 70 7-Aug-21 70-5 = 65 8-Aug-21 65-5+20 = 80 9-Aug-21 80-5 = 75 10-Aug-21 75-5 = 70  11-Aug-21 70-5 = 65     If the stock quantity is not getting zero then I want to display stock forecasts for next 2 months If the stock quantity becomes zero then I want to calculate on which day it will be zero Thanks in Advance

[Greg_Deckler]

@vrushabjain510 Seems like you want something like Days of Supply: https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

Basically a while loop.