Create a Measure stock availablity in the future depend on ETA

[Talal141218]

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.

[Talal141218]

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

[v-pagayam-msft · Accepted solution]

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.

[Talal141218]

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.

[Talal141218]

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