Return the amount for max date for each item and sum for group

[lucasattom]

Hello,

I'm having some trouble getting the following table to work.

I have the following database (it's simplified, I actually have information in different connected tables 😞

I want a matrix table summarized by the amount of each product for the max date (and within that date, I want the row with the max "Id"). The highlighted rows are the desired ones for the output.

The resulting matrix should look like this:

So I'd have a measure that returns the most recent amount (highest "date", then highest "Id") for each product, and summarize the group with the sum for each item. I tried using SUMMARIZE with no success.

Thanks in advance!

[v-rzhou-msft · Accepted solution]

Hi @lucasattom ,

I think you can try this code to create a measure.

Output =
VAR _AddColumn =
    ADDCOLUMNS (
        FILTER ( 'Table', 'Table'[Date] = MAX ( 'Table'[Date] ) ),
        "MAXID",
            CALCULATE (
                MAX ( 'Table'[Id] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Product] ),
                    'Table'[Date] = MAX ( 'Table'[Date] )
                )
            )
    )
VAR _Filter =
    FILTER ( _AddColumn, [Id] = [MAXID] )
RETURN
    SUMX ( _Filter, [Amount] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.