Stock Level Forecast (deduct monthly sales average from current stock monthly)

[Revolution]

I am trying to plot a stock level forecast which is simply the current stock level as a starting point. Each future month should deduct the sales average and add the incoming stock based on stock arrival dates.

I am using the following to calculate my rolling 12 month sales average:

Monthly Sales Average =

AVERAGEX(

    KEEPFILTERS(VALUES('Rolling12Dates'[Month])),

    CALCULATE(SUM('SalesOrdersFull'[Quantity Sold]))

)

Where Rolling12Dates is simply a Date table that only includes days from the last 12 months. I'm not sure how to go about creating this forecast. I don't think a measure will work. I have a value (stock level) for each product in each warehouse for the current month in my Inventory table. I need to use this as the first entry in a new table and deduct the monthly sales average for each future month as well as add any arriving stock to this (from the PurchaseOrders table). 

I created a date table that includes the next 6 months and attempted to use measures to subtract the average and add the purchase orders but it didn't work at all I ended up disregarding that progress due to frustration but am currently recovering the file to share here. I will try this again but please let me know if there is a better way.

[Revolution]

If I was writing code in C I would simply create an array then retrieve the last known value as the first entry. From there a simple loop to iterate through the future dates and -= the average from from the previous value. But DAX and Power Query aren't simple enough to logic my way out of this.

[v-yifanw-msft]

Hi @Revolution ,
Can you provide some detailed data (without sensitive information) combined with the results you are trying to get so that we can better understand and help you solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[Revolution]

I want to forecast stock levels by taking the current stock and deducting the daily sales average every day from the curent date for x number of days into the future.

I have a rolling 12 month AVERAGEX measure for the daily sales average and I have the current stock level for each stock item in an inventory table. I'm just not sure how to create this new table that begins with the current stock level today and will deduct the daily sales average from each subsequent day on my date table (which includes dates 6 months into the future).

I don't feel that sample data is necessary to point me in the right direction but I can do if it is required.

My next challenge will be to add incoming stock to the forecast...