Calculate products used on a daily basis

[v_mark]

Hello Everyone,

I have a simple table that has Quantity and Stock. The goal is to know how many products are being consumed on a daily basis. 
Here is the sample data table and desired result. Wondering how I can execute the desired result into a measure or calculated column using DAX or Power Query perhaps. 

Essentially this is how it was being computed in Excel.

And below is the result it was giving. 

LOB	Warehouse	Product	Date	Quantity	Stock	Result
A	Target	ABD-1155124	13/04/2023	3	11	8
B	Target	ABD-1155124	27/01/2023	1	11	7
B	Target	ABD-1155124	05/04/2023	1	11	6
B	Target	ABD-1155124	11/04/2023	1	11	5
B	Target	ABD-1155124	27/04/2023	1	11	4
B	Target	ABD-1155124	22/05/2023	1	11	3
B	Target	ABD-1155124	19/06/2023	1	11	2
B	Target	ABD-1155124	04/07/2023	1	11	1
B	Target	ABD-1155124	19/07/2023	1	11	0
B	Target	ABD-1155124	20/07/2023	1	11	-1
B	Target	ABD-1155124	01/08/2023	1	11	-2

[tamerj1]

Hi @v_mark 
Can you update the sample including the date and the product? Are you trying to create a calculated column or a measure?

[v_mark]

@tamerj1 - Sure I updated the original post. They have the same product all across and Warehouse. 
The difference they have is based in Dates and Line of business (LOB). 

[tamerj1]

@v_mark 
Why the quantity of LOB A which is (3) is subtracted twice? I mean shouldn't the result of the 2nd row be 7? What is the logic behind your expected result?

[v_mark]

@tamerj1 'm sorry that is a mistake on my end.  As far creating measure or calculated column either way will work  I guess. The idea is to drag it over to a table in the report. 
You are right.. Ideally it should be 7 . Please see details below.

I will update my post

[tamerj1 · Accepted solution]

@v_mark 
Please refer to attached sample file. See how it works with your real data and return back with your comments.

Result = 
VAR Stock = 'Table'[Stock]
VAR CurrentLOB = 'Table'[LOB]
VAR CurrentDate = 'Table'[Date]
VAR CurrentWarehoursProductTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Warehouse], 'Table'[Product] ) )
VAR TableBefore = 
    FILTER ( CurrentWarehoursProductTable, 'Table'[LOB] < CurrentLOB )
VAR TableOnAndBefore = 
    FILTER ( CurrentWarehoursProductTable, 'Table'[Date] <= CurrentDate && 'Table'[LOB] = CurrentLOB )
VAR QuantityBefore = 
    SUMX ( TableBefore, 'Table'[Quantity] )
VAR QuantityOnAndBefore = 
    SUMX ( TableOnAndBefore, 'Table'[Quantity] )
RETURN
    StocK - QuantityBefore - QuantityOnAndBefore