[Need help] Inventory Projection

[Jiro]

Currently I am currently struggling with some DAX codes, and really have no idea where I should go. Appreciate any kind guidance – maybe some rough idea/functions/step- so I can try to code some solutions.

*Date format is MM/DD/YYYY

Let’s consider the data I have on hands.

1. Inventory Data:

Record is in monthly granularity. The dating of stock period is showing fixed Feb 1st, but it is providing the data as of current date Feb 21st, which is the latest refresh. The dating just remains unchanged. Currently there is no information for March 2024 Inventory. 

Stock Period	Inventory On-Floor (MT)
02/01/2024	30,000

1. Outbound Data:

Record is in daily granularity. 

Outbound Date	Net Quantity (MT)
02/01/2024	200
02/02/2024	100
02/15/2024	500
02/19/2024	600
02/27/2024	200
02/31/2024	100
03/01/2024	100
03/04/2024	300
03/05/2024	700
03/15/2024	400

Current Measures:

I tried computing below measures, as inventory data is only available as a single row, dating Feb 1st, 2024. The measure seems fine to me if the projection is within current month; however, when it comes to the future month projection, for e.g., Mar 2024, you can see that the projection is producing negative values due to no stock data yet. 

Inventory Projection :=

VAR On-Floor Stock MTD = 
Calculate (
           SUMX ( Inventory, Inventory[Qty_Total] ),
           DATESMTD ( 'DATE' [Date] )
            )

VAR Outbound MTD = 
Calculate (
           SUMX ( Outbound, Outbound[Net_Qty] ),
           DATESMTD ( 'DATE' [Date] )
            )

VAR Result =
Inventory MTD - Outbound MTD

RETURN
            Result

Based on the above measures, the result should look like below which is not favorable due to Mar producing negative value. 

What I am looking for:

The way to compute DAX: If Mar 2024 stock data is not available, is there any chance we can use the projection at the end of Feb to compute projection for Mar 2024? As we are aiming to look into Mar and so on. Any other solution is also very much welcome.

[some_bih]

Hi @Jiro it seems you need something like column ShowValueForDates in your date / calendar table.

Check link for details

[Jiro]

Thanks @some_bih. I already have the date calendar and linked it with the inventory data.