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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.