[Need help] Inventory Projection

Jiro — Wed, 21 Feb 2024 07:02:47 GMT

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.

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

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.

Re: [Need help] Inventory Projection

some_bih — Wed, 21 Feb 2024 07:52:32 GMT

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

Check link for details

Re: [Need help] Inventory Projection

Jiro — Thu, 22 Feb 2024 12:18:37 GMT

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

topic [Need help] Inventory Projection in DAX Commands and Tips

[Need help] Inventory Projection

Re: [Need help] Inventory Projection

Re: [Need help] Inventory Projection