Forum Discussion
Inventory Forecast
Hello guys! How are you?
I'm trying to build a trade inventory forecast by month but nothing until now. I have an inventory table and the last position on nov,25. Then, to calculate I must add the month SI (Sales In) and subtract SO (Sales Out). Like this:
Stock Nov/25 = 39,2
SI Dec/25 = 21,9
SO Dec/25 = 23,6
Forecast Stock = 37,5
Stock Dec/25 = 37,5 (forecast stock)
SI Jan/26 = 22,4
SO Jan/26 = 23,8
Forecast Stock = 36,1
But in Dec, instead of take the calculated forecast stock, it takes the nov stock again.
I mean, the goal here is to use the actual stock when we have and the foward time needs to be forecast. So when I'm reach Jan/26 and I have the real closed stock for Dec/25, the formula must bring the stock from database and calculate forecast for Jan/26 and foward.
For tables, I'm using a calendar table (d_calendar) and for stock (f_tradeinventory), which has this structure:
Date | Snapshot | Region | Account | Item | Balance
01/11/2025 | 25/10/2025 | BR | BR01 | 762003528 | 3190
01/11/2025 | 25/10/2025 | BR | BR01 | 762004009 | 8304
This is the formula:
#TI =
DIVIDE(
CALCULATE(
SUM(f_tradeinventory[Saldo]),
d_Calendar[Date] = MAX(f_tradeinventory[Date])
),
1000000 //kt
)
I also did the following formula but this uses the hole timeline nas starts calculate from 2024. And I need just from when I don't have stock anymore:
#TI =
VAR DataMax = MAX(d_Calendar[Date])
-- 1. Ponto de partida único: O primeiro saldo real encontrado na história
VAR SaldoInicialHistorico =
CALCULATE(
SUM(f_tradeinventory[Saldo]),
FILTER(
ALL(d_Calendar),
d_Calendar[Date] = CALCULATE(MIN(f_tradeinventory[Date]), ALL(f_tradeinventory))
)
) / 1000000
-- 2. Soma de todas as entradas (SI) desde sempre até o mês da coluna atual
VAR SI_Acumulado_Total =
CALCULATE(
[#SI Ship],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] <= DataMax
)
)
-- 3. Soma de todas as saídas (SO) desde sempre até o mês da coluna atual
VAR SO_Acumulado_Total =
CALCULATE(
[#SO],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] <= DataMax
)
)
-- 4. Cálculo: Saldo Inicial + Tudo que entrou - Tudo que saiu
VAR Resultado = SaldoInicialHistorico + SI_Acumulado_Total - SO_Acumulado_Total
RETURN
IF(Resultado > 0, Resultado, BLANK())
Can you help me please?
Hi jefersonfs
The issue you're facing is common: your formula currently recalculates the entire history instead of "anchoring" to the last known physical balance and projecting forward.
To fix this, you need a Cut-off Logic. The formula should identify the last date with physical stock, grab that value, and then only calculate the cumulative SI (Sales In) and SO (Sales Out) from that point onward.
Recommended Solution
קטע קוד
#TI Forecast =
-- 1. Identify the last date where physical stock exists
VAR LastStockDate = CALCULATE(MAX(f_tradeinventory[Date]), ALL(f_tradeinventory))-- 2. Get the physical balance at that specific date
VAR LastStockValue =
CALCULATE(
SUM(f_tradeinventory[Saldo]),
FILTER(ALL(d_Calendar), d_Calendar[Date] = LastStockDate)
) / 1000000VAR CurrentDate = MAX(d_Calendar[Date])
-- 3. Calculate Cumulative Delta only for the period AFTER the last physical stock
VAR CumulativeSI =
CALCULATE(
[#SI Ship],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] > LastStockDate && d_Calendar[Date] <= CurrentDate
)
)VAR CumulativeSO =
CALCULATE(
[#SO],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] > LastStockDate && d_Calendar[Date] <= CurrentDate
)
)-- 4. Logic: Use actuals if they exist; otherwise, calculate Forecast
VAR ActualStock = [#TI]RETURN
IF(
CurrentDate <= LastStockDate,
ActualStock,
LastStockValue + CumulativeSI - CumulativeSO
)
If this does not fully resolve the behavior—especially when filtering by Item or Region—please share a sample PBIX file. Ensure it contains the same model logic but with no sensitive data, along with a clear example of the expected result versus the current output.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Hi jefersonfs
The issue you're facing is common: your formula currently recalculates the entire history instead of "anchoring" to the last known physical balance and projecting forward.
To fix this, you need a Cut-off Logic. The formula should identify the last date with physical stock, grab that value, and then only calculate the cumulative SI (Sales In) and SO (Sales Out) from that point onward.
Recommended Solution
קטע קוד
#TI Forecast =
-- 1. Identify the last date where physical stock exists
VAR LastStockDate = CALCULATE(MAX(f_tradeinventory[Date]), ALL(f_tradeinventory))-- 2. Get the physical balance at that specific date
VAR LastStockValue =
CALCULATE(
SUM(f_tradeinventory[Saldo]),
FILTER(ALL(d_Calendar), d_Calendar[Date] = LastStockDate)
) / 1000000VAR CurrentDate = MAX(d_Calendar[Date])
-- 3. Calculate Cumulative Delta only for the period AFTER the last physical stock
VAR CumulativeSI =
CALCULATE(
[#SI Ship],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] > LastStockDate && d_Calendar[Date] <= CurrentDate
)
)VAR CumulativeSO =
CALCULATE(
[#SO],
FILTER(
ALL(d_Calendar),
d_Calendar[Date] > LastStockDate && d_Calendar[Date] <= CurrentDate
)
)-- 4. Logic: Use actuals if they exist; otherwise, calculate Forecast
VAR ActualStock = [#TI]RETURN
IF(
CurrentDate <= LastStockDate,
ActualStock,
LastStockValue + CumulativeSI - CumulativeSO
)
If this does not fully resolve the behavior—especially when filtering by Item or Region—please share a sample PBIX file. Ensure it contains the same model logic but with no sensitive data, along with a clear example of the expected result versus the current output.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hello Rita!
It seems your solution is the one. Let me test a little bit over here today and I'll get back to mark it.
Thanks a lot!Hello again!
It will take some time to receive a new batch of data to do more tests.
So I will mark your solution because, as far as I tested it works.
If I have some problems in the future I will raise a new post.
Thanks a lot!
Hi,
I have solved a similar question in the attached files.
Hope this helps.
