Increase and decrease based on values of other column

[Hamdan1234]

I have two tables. I want a column or measure based on, if there are sales in INV_Type column then value of beginning inventory should be decreased based on Qty column. So if QTY column has 3 and INV_Type is sales then the beginning inventory should be reduced by 3. If INV_Type is  out it should also decrease from beginning inventory ,if purchase or INN is there in INV_Type then it should add based on quantity column for each Item ID Type. It should be taking beginning inventory from second table. The CO_ID represents years. We can also fixed the beginning inventory for each item type like if we fix it to 100 for 2019 based on INV_Type it should keep on adding or subtracting. Please let me know if possible. 
Thanks

Year               ITEM_ID  INV_ID QTY   INV_TYPE  CO_ID

17/01/2019	8000	1	2	Sales	2019
18/02/2019	9900	2	3	Sales	2019
20/10/2019	9900	3	1	INN	2019
21/11/2019	8000	4	4	Purchase	2019
25/02/2020	9900	5	5	Sales	2020
11/05/2020	8000	6	3	Sales	2020
10/10/2020	9900	7	7	Purchase	2020
19/11/2020	9900	8	5	out	2020
17/01/2019	8000	9	1	Sales	2019
19/02/2019	8000	10	2	Sales	2019
22/10/2019	8000	11	2	INN	2019
21/11/2019	8000	12	5	Purchase	2019

ITEM_IDBEG_QTYCO_ID

9900	100	2019
8000	50	2019
9900	10	2020
8000	5	2020

[v-zhangti · Accepted solution]

Hi, @Hamdan1234 

You can try the following methods.

change =
IF (
    [INV_TYPE] = "Sales",
    - [QTY],
    IF (
        [INV_TYPE] = "out",
        - [QTY],
        IF (
            [INV_TYPE] = "Purchase",
            + [QTY],
            IF ( [INV_TYPE] = "INN", + [QTY], BLANK () )
        )
    )
)

Sum change =
CALCULATE (
    SUM ( 'Table 1'[change] ),
    FILTER (
        'Table 1',
        [ITEM_ID] = EARLIER ( 'Table 2'[ITEM_ID] )
            && [CO_ID] = EARLIER ( 'Table 2'[CO_ID] )
    )
)

Final Oty = [BEG_QTY]+[Sum change]

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Quick reminder that Power BI is a reporting tool, not a stock/inventory management tool.

What's the start date for your years? Jan 1st?