Forum Discussion
Inventory Forecast
Hi Bro
I would like to create the ivnetory forecast base on the in and out record, how to create below report in PowerBI, please help and share the code to me, Thanks
From Now on until the end of FY25 ( 31-Mar 2025)
| Jul | End of Jul | End of Aug | |||||
| Currently Qty | In | Out | Balance Qty | In | Out | Balance Qty | |
| Item A | 50 | 10 | 30 | 30 | 20 | 10 | 40 |
| Item B | 10 | 30 | 20 | 20 | 60 | 10 | 70 |
2 Replies
Hi Roger_EA -create two measure for in and outas per your requirement
TotalIn =
CALCULATE(
SUM(InventoryTransactions[Quantity]),
InventoryTransactions[TransactionType] = "In"
)total out measure:
TotalOut =
CALCULATE(
SUM(InventoryTransactions[Quantity]),
InventoryTransactions[TransactionType] = "Out"
)create a measure for balance forcast measure as below
BalanceQty =
VAR LastDate = MAX(Calendar[Date])
RETURN
SUMX(
VALUES(Items[Item]),
Items[CurrentQty] +
CALCULATE(
[TotalIn] - [TotalOut],
FILTER(
ALL(Calendar),
Calendar[Date] <= LastDate
)
)
)Hope it helps to get the expected forcast.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Hi Roger_EA ,
Can you provide some sample data? We can better understand the problem and help you.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
Please also show your desired results so we can help you better.Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
