Forum Discussion
Inventory Forecast Weekly
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.
3 Replies
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) * DaysInWeekthen 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 - WeeklySalesForecastThanks 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.
JGomez_2012 ，
Please share the pbix sample file for better testing.
