Projected stock calculation help request

[ifenek]

Hello, 

I need help, I'm trying to implement a stock projection calculation in place. 

What I'm looking to do is to say if I have enough stock to despatch a product. For that i'm looking at previous orders for the same product. If the due date of the despatch is the same the idea is to look at the earliest activationdate to determine an order. 

I have a column showing the available stock for the particular product. This value should be decreasing in regards of the previous orders and not stay the same like it is right now. 

I've attached a sample file as well as an excel spreadsheet with the calculation I'd like to achieve 

https://we.tl/t-g9aFPxZqwt

status	duedate	Activationdate	traderid	sorder	itemnumber	partid	remaingquantity	availableforassembly	Calculation	Result	Despatch?
ACTIVE	01/10/2021	2021-09-30 08:11:00.000	CUST1	S-0001	7	SRP124	100	1000	/	1000	Ok
ACTIVE	02/10/2021	2021-09-30 08:12:00.000	CUST3	S-0002	15	SRP124	200	1000	1000-100-300	600	Ok
ACTIVE	03/10/2021	2021-09-30 08:23:00.001	CUST2	S-0003	1	SRP124	1050	1000	1000-100-200-300	400	Nok
ACTIVE	04/10/2021	2021-09-30 10:12:00.001	CUST1	S-0004	1	SRP124	150	1000	1000-100-200-1050-300	-650	Nok
ACTIVE	01/10/2021	2021-09-30 10:23:00.002	CUST1	S-0005	3	SRP124	300	1000	1000-100	900	Ok

[v-angzheng-msft · Accepted solution]

Hi, @ifenek 

Try to create a measure like this:

_Result = 
var _max=MAX ( 'Table'[availableforassembly] )
var _count=
        CALCULATE (
        DISTINCTCOUNT ( 'Table'[Activationdate] ),
        ALLEXCEPT ( 'Table', 'Table'[duedate] )
    )
var duedate=
    CALCULATE (
            SUM ( 'Table'[remaingquantity] ),
            FILTER ( ALL ( 'Table' ), 'Table'[duedate] < MAX ( 'Table'[duedate] ) )
        )
var duedate_Act=
    CALCULATE (
            SUM ( 'Table'[remaingquantity] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[duedate] = MAX ( 'Table'[duedate] )
                    && 'Table'[Activationdate] < MAX ( 'Table'[Activationdate] )
            )
        )
return 
IF(_count>1,_max-duedate-duedate_Act,_max-duedate)

resutl:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.