Only use most recent transaction for some subcategories

[JP-Siloam]

I've got some data that I'm trying to report on together that's coming in from different sources.  For almost all sources I'm getting similar enough data that it doesn't matter, but from one source, I'm getting Balances instead of transactions.  So I need to build a measure that will sum the amounts of all transactions, except for one category.  Here's some example data:

For Source/Category A, I want Sum([Amount]), but Source/Category C I only want the most recent transaction in each to be used. So Y should show as 25, Z should show as 25 and C should show as 50.  The overall total should be 300.

Data model is pretty simple: 
Date and Cat are dimension tables
Transactions is the fact table with Cat2, Date, Amount as fields

A date slicer on the report sets the Max Date on the date table to be considered.  Min dates will never change.

 
I've been working under the assumption that I need something like this:

IF(

    'Cat'[Cat 1] = "C",
    fancy expression,
    SUM('Transactions'[Amount])
)

I've tried about a dozen different things for that fancy expression, the closest I got was adapting the TopN ranking pattern from:
https://www.daxpatterns.com/ranking/

But even that was never quite right.

I'm so brain dead on this now I need a fresh pair of eyes to point out whatever compeltely obvious thing I've missed 🙂

Thanks!

Julie

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result measure: =
VAR _t =
    SUMMARIZE (
        CALCULATETABLE ( 'Transaction', ALL ( 'Calendar' ) ),
        Category[Cat 1],
        Category[Cat 2],
        'Calendar'[Date]
    )
VAR _conditionA =
    ADDCOLUMNS (
        FILTER ( _t, Category[Cat 1] = "A" ),
        "@expectedresult", CALCULATE ( SUM ( 'Transaction'[Amount] ) )
    )
VAR _conditionC =
    ADDCOLUMNS (
        FILTER ( _t, Category[Cat 1] = "C" ),
        "@expectedresult",
            CALCULATE (
                SUM ( 'Transaction'[Amount] ),
                KEEPFILTERS (
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[Date]
                            =
                            VAR _maxdateconditoinC =
                                MAXX (
                                    FILTER (
                                        ALL ( 'Transaction' ),
                                        'Transaction'[Cat 1] = EARLIER ( Category[Cat 1] )
                                            && 'Transaction'[Cat 2] = EARLIER ( Category[Cat 2] )
                                    ),
                                    'Transaction'[Date]
                                )
                            RETURN
                                _maxdateconditoinC
                    )
                )
            )
    )
VAR _all =
    UNION ( _conditionA, _conditionC )
RETURN
    IF (
        HASONEVALUE ( Category[Cat 1] ),
        SUMX (
            FILTER ( _all, 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) ),
            [@expectedresult]
        ),
        SUMX ( _all, [@expectedresult] )
    )