Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.