cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors