Need to Create an Inventory Forecast by weeks based on an Actual Stock, new orders

[Anonymous]

Hello team,

i have this issue that need to be solve, i have try every combination of measures and still stock and how to make my forecast move on.  

Basically the only table where i have a date is the Orders_to_come.  All the models that i saw about forecasting use the sales from previous years but i don't have that data, just the data that i show below.

So at the end i need to present something like this  ( the numbers in each time intelligence column should be the forecast inventory.  

item/brand/loc	year-week
	2024-30	2024-31	2024-32	2024-33		2025-01	2025-02
x2565	5	15	12	0	32	10	23
x2312	14	52	63	52	12	45	86

For example i have this table for stocks

item_id	item_loc	brand	type	quantity
x2565	200	x_brand	tshirt	10
x2565	100	x_brand	tshirt	15
x2312	200	y_brand	shirt	20

forecast__demand_month

item_id	item_loc	project_montly_sales
x2565	200	10
x2565	100	5
x2312	200	6

Orders_to_come

item_id	item_loc	expected_date	quantity
x2565	200	08/20/2024	100
x2565	100	09/20/2024	50
x2312	200	08/15/2024	12

if any one could show me a way to start this part it would be wonderfull.  

[bhanu_gautam]

@Anonymous ,First make sure you have one date table

DAX
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2024, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Year-Month", FORMAT([Date], "YYYY-MM")
)

Create a mesure for Weekly Forecast

 WeeklyForecastDemand =

    DIVIDE (

        SUM('forecast_demand_month'[project_montly_sales]),

        4

    )

Then calculate inventory forecast

ForecastInventory =
VAR CurrentWeek = MAX('DateTable'[Date])
VAR InitialStock =
CALCULATE (
SUM('stocks'[quantity]),
FILTER (
'stocks',
'stocks'[item_id] = MAX('forecast_demand_month'[item_id]) &&
'stocks'[item_loc] = MAX('forecast_demand_month'[item_loc])
)
)
VAR IncomingOrders =
CALCULATE (
SUM('Orders_to_come'[quantity]),
FILTER (
'Orders_to_come',
'Orders_to_come'[item_id] = MAX('forecast_demand_month'[item_id]) &&
'Orders_to_come'[item_loc] = MAX('forecast_demand_month'[item_loc]) &&
'Orders_to_come'[expected_date] <= CurrentWeek
)
)
VAR ForecastedDemand =
CALCULATE (
SUMX (
DATESINPERIOD('DateTable'[Date], CurrentWeek, -7, DAY),
[WeeklyForecastDemand]
),
FILTER (
'forecast_demand_month',
'forecast_demand_month'[item_id] = MAX('forecast_demand_month'[item_id]) &&
'forecast_demand_month'[item_loc] = MAX('forecast_demand_month'[item_loc])
)
)
RETURN
InitialStock + IncomingOrders - ForecastedDemand

use this in matrix visual

[Anonymous]

Hi Bhanu,

Thanks for your answer,  I tried the approach but the initial inventory it's not moving correctly.

i'm trying other stuffs.

[JGomez_2012]

Thanks Bhanu,

I create the calendar table and make all the DAX measures, but the initial stock that is supposed to move each week with the final result from last week is not working.  it's supposed to do like this

								ORDERS TO COME
ITEM	LOCATION	DEMAND	WEEK	ON HAND				7/29/2024	8/11/2024	8/18/2024	9/27/2024	10/2/2024
RSS	100	401,896.00	100,474.00	348,783.00				163200	365160	579360	320280	206040
					30	31	32	33	34	35	36	37	38
					248,309.00	311,035.00	210,561.00	475,247.00	954,133.00	853,659.00	753,185.00	652,711.00	552,237.00

but it's generating something this