topic Re: Create a Measure stock availablity in the future depend on ETA in DAX Commands and Tips

Create a Measure stock availablity in the future depend on ETA

Talal141218 — Mon, 19 May 2025 08:34:56 GMT

Hello Profis,

Please, I need your help here to create a measure and calculate the quantity of stocks in the future as a forecast.

I have a table called F-PurchOrderLine, which includes a column (ETA). Furthermore, I copied two measures to this table: the first one is Open Sales Order Quantity from the table F-SalesOrderLine, and the second measure is Stock Quantity from the table F-Stocks.

Please consider that there are no relationships between the tables. Note: The D-Item table, which I want to use to calculate stock availability, has a relationship with all the mentioned tables. The measure is as follows:

Stock availability =

var Stock = [Lager Bestand exlu.Rev.] var arrivalItems = Fact_PurchOrderLine[CM_PurchOrderline_Qty_piec] var openSalesOrder = CALCULATE([CM_SalesLine_SalesOrderLine_CountOpen], Fact_SalesOrderLine[ShippingDateRequested_NK] >= TODAY()) return Stock + arrivalItems - openSalesOrder

My problem is: the open sales order is deducted for each day. For example: 12/07/2025 Stock = 500, arrival (purchased) items = 100, and open sales order = 50. Although the open order quantity is 50 pieces on 12/07/2025, it will be deducted on 19/05/2025, 20/05/2025, etc. Please consider that the open sales order date is a column in the F-SalesOrderLine table. Please, if you need more info, don't hesitate to contact me. Thanks in advance.

Re: Create a Measure stock availablity in the future depend on ETA

Anonymous — Mon, 19 May 2025 11:51:42 GMT

Hi Talal141218 ,
Thank you for reaching out to us Microsoft Fabric Community Forum!

Upon my understanding, I tried to recreate it on my local.So I followed below steps:
1.Create a measure for forecast stock availablity using below:

Forecasted Stock Availability =

VAR CurrentDate = SELECTEDVALUE('D-Date'[Date])

VAR Stock = [Base Stock]

VAR Incoming = CALCULATE(

    SUM('F-PurchOrderLine'[CM_PurchOrderline_Qty_piec]),

    'F-PurchOrderLine'[ETA] <= CurrentDate

)

VAR Sales = CALCULATE(

    SUM('F-SalesOrderLine'[SalesOrderQty]),

    'F-SalesOrderLine'[ShippingDateRequested_NK] <= CurrentDate

)

RETURN

    Stock + Incoming - Sales

2.Create a measure for sales order:

Sales Orders Up To Date =

CALCULATE(

    SUM('F-SalesOrderLine'[SalesOrderQty]),

    'F-SalesOrderLine'[ShippingDateRequested_NK] <= MAX('D-Date'[Date])

)

3.Create a measure for purchase order:

Purchases Up To Date =

CALCULATE(

    SUM('F-PurchOrderLine'[CM_PurchOrderline_Qty_piec]),

    'F-PurchOrderLine'[ETA] <= MAX('D-Date'[Date])

)

Please find the attached PBIX file and screenshot for your reference :

I hope this answer mewts your requirements.If so,give us kudos and consider accepting it as solution.

Regards,
Pallavi.

Re: Create a Measure stock availablity in the future depend on ETA

Talal141218 — Mon, 19 May 2025 12:42:39 GMT

Thanks for your Support. Unfortunately, the solution you suggested doesn't work with the model I have.. As you see in the screenshot :

More info: ETA is a column in F-PurchOrderLine and the sales open order date is also a column in F-SalesOrderLine. There is no relation between the two tables. The date [Date_NK] does not identify the forecast from neither Sales or Purchase. For your information, I don't have access to date transformation.

Re: Create a Measure stock availablity in the future depend on ETA

Talal141218 — Mon, 19 May 2025 12:45:02 GMT

Forethermore i filtered the date after two years, becauase i got error mesage.

Re: Create a Measure stock availablity in the future depend on ETA

Talal141218 — Tue, 20 May 2025 14:30:09 GMT

I have adapted the formula with my database and it's work now. Thanks a lot.