count stock level based on invoice type (sales-purchased,etc)

[YaserB]

Hi,

I'm new to power bi and stuck in very important information for me

i want to know the stock level for my product on any date

on our database we only see 2 information regarding the stock on tow Different data table 

1- the transaction (sales or purchased )

Year	ITEM_ID	INV_ID	QTY	INV_TYPE
25-02-2020	9900	1	10	Sales
11-05-2020	9900	2	20	Sales
10-10-2020	9900	10	100	Purchase
17-11-2020	9900	3	70	Sales

2- the beginning of the quantity moved from last year in this case we moved 100 QTY from 2019 to 2020 

ITEM_ID	BEG_QTY
9900	100

the result i expected something like this in RED color               Stock from last year 100 PCs

Year	ITEM_ID	INV_ID	QTY	INV_TYPE	Stock on Hand
25-02-2020	9900	1	10	Sales	90
11-05-2020	9900	2	20	Sales	70
10-10-2020	9900	10	100	Purchase	170
17-11-2020	9900	3	70	Sales	100

here's the link for the excel and pbix 

https://drive.google.com/drive/folders/1NVoFWiJF8PN4Rr3MFe16WzPNFr6QUjAC?usp=sharing

 please if you need more information let me know

Thank you.

[YaserB]

i cant thank you enough, i spent many hours and days searching on youtube and Internet

it take me sometime to understand what you said (this is the first ever DAX i do it my self) and it work perfectly on the sample data i need just to try it on the Big data 

i will continues search on the Internet but you can save me sometime

how i can do this part you mentioned

"you may need to match the txn ID from the 2nd table to the ITEM_ID in the first table."

 Thank you again

Update:

i found the way to integrate tow table

my solution was

VAR _txn = RELATED(Sheet2[BEG_QTY])

from this thread i found my solution 

https://community.powerbi.com/t5/Desktop/Create-a-Calculated-Column-from-Different-Tables/m-p/60773#...

[HotChilli · Accepted solution]

As a calculated column:

ColumnC = 
VAR _rowDate = TableQ[Year]
VAR _txn = ......
VAR SalesToDate = CALCULATE(SUM(TableQ[QTY]), FILTER(TableQ, TableQ[INV_TYPE] = "Sales" &&  TableQ[Year] <= _rowDate))
VAR PurchasesToDate = .........
RETURN
_txn - SalesToDate + PurchasesToDate

I've left parts of it for you to do.

_txn is the value from the 2nd table.

PurchasesToDate is similar to SalesToDate

----

I didn't look at the attached data so if it's more complex, you may need to match the txn ID from the 2nd table to the ITEM_ID in the first table.

Let me know how you get on