Forum Discussion
Seek Advice: Inventory Projection
Dear All,
I was trying to make inventory projection for the logistics operations, and I am currently struggling with some DAX codes. Appreciate your kind guidance.
*Date format is MM/DD/YYYY
1. Inventory Data:
Record is in monthly granularity. The data is providing the latest stock as of the latest refresh time. Currently there is no information for February 2024 Stock.
| Stock Period | Inventory On-Floor (MT) |
| 01/01/2024 | 30,000 |
2. Outbound Data:
Record is in daily granularity.
| Outbound Date | Net Quantity (MT) |
| 01/01/2024 | 200 |
| 01/02/2024 | 100 |
| 01/15/2024 | 500 |
| 01/19/2024 | 600 |
| 01/27/2024 | 200 |
| 01/31/2024 | 100 |
| 02/01/2024 | 100 |
| 02/04/2024 | 300 |
| 02/05/2024 | 700 |
| 02/15/2024 | 400 |
Current Measures:
I tried computing below measures, as inventory data is only available in a monthly basis. The granurality is at the first date of January 2024; however, it is giving the latest available as of the latest refresh time. The measure seems fine to me; however, when it comes to the future month projection, for e.g. Feb 2024, you can see that the projection is producing negative values due to no stock data for February yet.
On-Floor MTD :=
Calculate (
SUMX ( Inventory, Inventory[Qty_Total] ),
DATESMTD ( 'DATE' [Date] )
)
Outbound MTD :=
Calculate (
SUMX ( Outbound, Outbound[Net_Qty] ),
DATESMTD ( 'DATE' [Date] )
)
Projection := Inventory MTD - Outbound MTD
Based on the above measures, the result should look like below which is not favorable due to Feb producing negative value.
What I am looking for:
1. The way to compute DAX that, if Feb 2024 stock data is not available we can take the projection at the end of Jan to be used instead so that we have a look into future inventory where stock data is not yet available (Feb/Mar/Apr) - as long as there are outbound. Any other method is also very much welcome.
2. This aspect aside, is there also any way that we can let the chart shows only data from current date onwards?
Thank you in advance!
*Edited in laptop for readability
2 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
