topic Re: Inventory Forecast Weekly in DAX Commands and Tips

Inventory Forecast Weekly

JGomez_2012 — Fri, 02 Aug 2024 12:45:38 GMT

Hello Community,

I need to create a weekly forecast of my inventory based on 3 tables:

Actual Inventory

Arrived Orders

Sale Forecast

The structure of the tables is like this:

Actual Inventory (

item varchar(),

loc varchar()

quantity_on_hand int

)

Sales Forecast

(

item varchar()

loc varchar()

MontlySales int

)

Arrived Orders

(

item varchar()

loc varchar()

quantity_expected int

expected_date datetime

)

Based on this information i have to project the next 6 months on a weekly basis. My actual problem is that i need the inventory for next week is the result of the following measures: Week_forecast = initial_inventory + orders_to_come - weekly_forecast.

I created a CalendarDate table to follow all the measures in the timeframe. All the tables are related on item + loc and the CalendarDate is related to the Arrived Orders table.

This is what i'm getting in power bi

when i should be getting this.

Anyone knows i way where i can do like a cycle or something to do this.

Thanks in advance.

Re: Inventory Forecast Weekly

PurpleGate — Fri, 02 Aug 2024 13:03:37 GMT

Hey,

I had a similar situation to yours and solved it like this: maybe this will work for you too?

first i made a Weekly Sales Forecast.

Weekly_Sales_Forecast = 
VAR MonthlyForecast = SUM('Sales Forecast'[MontlySales])
VAR DaysInMonth = DAY(EOMONTH(MIN('CalendarDate'[Date]), 0))
VAR DaysInWeek = COUNTROWS(VALUES('CalendarDate'[Date]))

RETURN
DIVIDE(MonthlyForecast, DaysInMonth, 0) * DaysInWeek

then a measure to calculate the quanity of orders each week:

Weekly_Arrivals = 
CALCULATE(
SUM('Arrived Orders'[quantity_expected]),
FILTER(
'Arrived Orders',
'Arrived Orders'[expected_date] >= MIN('CalendarDate'[Date]) &&
'Arrived Orders'[expected_date] <= MAX('CalendarDate'[Date])
)
)

Then an initial inventory for each item at the start of the week:

Initial_Inventory = 
CALCULATE(
SUM('Actual Inventory'[quantity_on_hand]),
FILTER(
'Actual Inventory',
'Actual Inventory'[item] = MAX('CalendarDate'[item]) &&
'Actual Inventory'[loc] = MAX('CalendarDate'[loc])
)
)

Then combined it to get the weekly forecast

Weekly_Forecasted_Inventory = 
VAR InitialInventory = [Initial_Inventory]
VAR WeeklyArrivals = [Weekly_Arrivals]
VAR WeeklySalesForecast = [Weekly_Sales_Forecast]

RETURN
InitialInventory + WeeklyArrivals - WeeklySalesForecast

Re: Inventory Forecast Weekly

JGomez_2012 — Fri, 02 Aug 2024 13:21:24 GMT

Thanks for your answer, but got the same issue the weekly forecast is not moving forward. with your measures i have the same results that i had before.

Re: Inventory Forecast Weekly

Anonymous — Fri, 09 Aug 2024 09:28:12 GMT

JGomez_2012 ，
Please share the pbix sample file for better testing.