Seek Advice: Inventory Projection

[Jiro]

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 

[lbendlin]

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-...

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/1447...

[Jiro]

Hey @lbendlin,

Thanks for the advice as well as response here. I just had time to edit the post. I will try to be more explicit about it.