Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, new to the forum.
Im having trouble dealing with closing balance of inventory when the forward forecast results in negatives.
I have:
-current starting Inventory
-monthly sales forecast
-monthly incoming inventory
Im using the following
InvMovement:=[PURCHASES]-[SALES]
OPENING INV:=calculate([InvMovement],FILTER(ALL('DATE'[DATE]),'DATE'[DATE]< MAX('DATE'[DATE])))+[CURRINV]
ENDING INV:=calculate([InvMovement],FILTER(ALL('DATE'[DATE]),'DATE'[DATE]<= MAX('DATE'[DATE])))+[CURRINV]
the problem is that there cases where my Sales Team is forecasting above the available inventory for a future period. When we are out of Stock we still want the forecast to remain, even though we cant fill the sale.
The above OPENING INV and ENDING INV calculations work when inventory is always positive, but accumulates the negatives on Out of Stocks and results in incorrect (negative) opening inventory positions.
Id like the ENDING INV results to be 0 if it calculates as negative and ENDING INV of the following period to also be zero.
any suggestions?
Thanks
Hi,
Share a simple dataset and show the expected result there.
Thanks For responding. This is what Im getting:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
OPENING INV | 120 | 20 | 120 | 20 | -80 | -180 | -280 | 20 | -80 | -180 | -280 | -380 |
FORECAST | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
PURCHASES | 0 | 200 | 0 | 0 | 0 | 0 | 400 | 0 | 0 | 0 | 0 | 0 |
ENDING INV | 20 | 120 | 20 | -80 | -180 | -280 | 20 | -80 | -180 | -280 | -380 | -480 |
FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE |
This is the desired result:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
OPENING INV | 120 | 20 | 120 | 20 | 0 | 0 | 0 | 300 | 200 | 100 | 0 | 0 |
FORECAST | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
PURCHASES | 0 | 200 | 0 | 0 | 0 | 0 | 400 | 0 | 0 | 0 | 0 | 0 |
ENDING INV | 20 | 120 | 20 | 0 | 0 | 0 | 300 | 200 | 100 | 0 | 0 | 0 |
FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE | FUTURE |
Hi,
Share the link from where i can download your PBI file.
Hi was unable to send the PBI file but I mocked up a ppvt file for an illustration of what I want.
Hi, unfortunately im unable to share files as per my company's privacy policies.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |