cancel
Showing results for
Did you mean:

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

Helper II

## Sum by multiple criteria of the last transaction date

How do I create a measure sum "Qty" of each "Product", and "Block" of the last "TxnDate"? Please help thanks.

Data Table:

 TxnDate Qty SerialN Product Block 1/1/23 6 111 AAA BlockA 1/2/23 5 111 AAA BlockA 1/3/23 3 111 AAA BlockA 1/4/23 6 111 AAA BlockA 1/5/23 5 222 AAA BlockA 1/6/23 7 222 AAA BlockA 1/7/23 3 333 BBB BlockA 1/8/23 6 333 BBB BlockA 1/9/23 7 333 BBB BlockA 1/10/23 4 444 BBB BlockA 1/11/23 7 444 BBB BlockA 1/1/23 4 555 AAA BlockB 1/2/23 5 555 AAA BlockB 1/3/23 6 555 AAA BlockB 1/4/23 3 555 AAA BlockB 1/5/23 5 666 AAA BlockB 1/6/23 5 666 AAA BlockB 1/7/23 8 777 BBB BlockB 1/8/23 4 777 BBB BlockB 1/9/23 6 777 BBB BlockB 1/10/23 8 888 BBB BlockB 1/11/23 4 888 BBB BlockB

Result: 2 measures

AAA_Qty = Sum of product AAA last TxnDate

BBB_Qty = Sum of product BBB last TxnDate

 Block AAA_Qty BBB_Qty BlockA 13 14 BlockB 8 10

1 ACCEPTED SOLUTION
Super User

@EZiamslow You could use a matrix with Block as rows and Product as columns and this measure:

``````Measure =
VAR __Table =
SUMMARIZE('Data', [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result``````

Or, if you really want two measures:

``````Measure AAA =
VAR __Product = "AAA"
VAR __Table =
SUMMARIZE(FILTER( 'Data', [Product] = __Product), [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result

Measure BBB =
VAR __Product = "BBB"
VAR __Table =
SUMMARIZE(FILTER( 'Data', [Product] = __Product), [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@EZiamslow You could use a matrix with Block as rows and Product as columns and this measure:

``````Measure =
VAR __Table =
SUMMARIZE('Data', [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result``````

Or, if you really want two measures:

``````Measure AAA =
VAR __Product = "AAA"
VAR __Table =
SUMMARIZE(FILTER( 'Data', [Product] = __Product), [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result

Measure BBB =
VAR __Product = "BBB"
VAR __Table =
SUMMARIZE(FILTER( 'Data', [Product] = __Product), [SerialN], "__MaxTxnDate", MAX('Data'[TxnDate])),
"__Qty", SUMX(FILTER( 'Data', [SerialN] = EARLIER([SerialN]) && [TxnDate] = [__MaxTxnDate]), [Qty])
)
VAR __Result = SUMX(__Table, [__Qty])
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

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