Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
JGomez_2012
Frequent Visitor

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

JGomez_2012_0-1722602374096.png

when i should be getting this.

JGomez_2012_1-1722602684289.png

 

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

 

Thanks in advance.

 

3 REPLIES 3
v-yajiewan-msft
Community Support
Community Support

@JGomez_2012 ,
Please share the pbix sample file for better testing.

PurpleGate
Resolver III
Resolver III

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.

 

JGomez_2012_0-1722604870238.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.