Sum by multiple criteria of the last transaction date

[EZiamslow]

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

[Greg_Deckler · Accepted solution]

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

Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            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 = 
        ADDCOLUMNS(
            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 = 
        ADDCOLUMNS(
            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