Create a measure to get sum of values from two columns

[Debo1996]

I have two tables named:

1. Item Master (This has item name, unique code, opening amount and opening quantity at a fixed date (1-Apr-2022) )

2. Transactions (Item transactions from 1-Apr-2022 to 31-Mar-2023)

Sample: 

Item Master Table
Code	Item Name	Opening Quantity	Opening Value
A-001	Paint Red	5	500
A-002	Paint Blue	3	300
A-003	Paint Green	2	200
B-001	Brush Small	100	1000
B-002	Brush Medium	122	2440

Transaction Table
Date	Transaction Type	Code	Quantity	Rate	Amount
01/04/22	Sale	A-001	2	105	210
01/04/22	Sale	A-002	2	105	210
01/04/22	Sale	A-003	2	105	210
04/04/22	Purchase	B-001	5	10	50
04/04/22	Purchase	B-002	5	20	100
10/04/22	Sale	B-001	50	11	550
10/04/22	Sale	B-002	40	22	880
10/04/22	Sale	A-001	1	105	105

Now I want to create a measure to get closing balance as of any date for all stock items. 

What will be the DAX formula for such a measure? (Assume date table exists with name Date Table)

Thank you!!

[tamerj1 · Accepted solution]

Hi @Debo1996 

please try

Closing Quantity =
MAX ( 'Item Master'[Opening Quantity] )
+ CALCULATE (
SUMX (
Transaction,
IF (
Transaction[Transaction Type] = "Sale",
Transaction[Transaction Quantity],
- Transaction[Transaction Quantity]
)
),
Transaction[Date] <= MAX ( Transaction[Date] )
)