Sum rows that have the max date by category

[dpc_development]

I have a fact table with a date column, a category column, and a numeric column. I want to filter the fact table for only the row that has the max date per category, and then sum up the values in the numeric column.

As can be seen in the picture below, the most recent dates are highlighted and the desired total comes up to 152.

I started by using SummarizeColumns to find the most recent date per category, but couldn't figure out how to filter the fact table and sum up the number column.

MyMeasure =
SUMMARIZECOLUMNS(
    'FACT'[Category],
    "Max Date", MAX('FACT'[Date])
)

[Fowmy · Accepted solution]

@dpc_development 

Add this measure:

MaxByCategory = 

SUMX (
    FILTER (
        Fact,
        VAR __V = CALCULATE ( MAX ( Fact[date] ), ALLEXCEPT ( Fact, Fact[category] ) ) RETURN
        Fact[date] = __V 
    ),
    Fact[number]
)

[dpc_development]

I was able to get a solution like this, but not sure whether it is the best DAX code for this.

Remaining Stock = 
SUMX(
    SUMMARIZE(
        'FACT',
        [Category],
        "Max Date", MAX('FACT'[Date])
    ),
    LOOKUPVALUE('FACT'[NumericColumn],'FACT'[Category], [Category],'FACT'[Date], [Max Date])
)