Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
Solved! Go to 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] )
)
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] )
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |