Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Debo1996
Regular Visitor

Create a measure to get sum of values from two columns

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
CodeItem NameOpening QuantityOpening Value
A-001Paint Red5500
A-002Paint Blue3300
A-003Paint Green2200
B-001Brush Small1001000
B-002Brush Medium1222440

 

 

Transaction Table
DateTransaction TypeCodeQuantityRateAmount
01/04/22SaleA-0012105210
01/04/22SaleA-0022105210
01/04/22SaleA-0032105210
04/04/22PurchaseB-00151050
04/04/22PurchaseB-002520100
10/04/22SaleB-0015011550
10/04/22SaleB-0024022880
10/04/22SaleA-0011105105

 

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!!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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] )
)

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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] )
)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.