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

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

1 ACCEPTED SOLUTION
Super User

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

1 REPLY 1
Super User

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’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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors