March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
15 | |
12 | |
9 | |
8 |
User | Count |
---|---|
41 | |
32 | |
29 | |
12 | |
12 |