Calculate one distinct date in a measure

[Anonymous]

Hi all,

I have a scenario where i want to sum the Amounts for customers based on when they did a transaction. I've successfully calulated this using the following measure 
Sum_LastTransactions =

CALCULATE (

SUM('Data'[Amount]),

GENERATE (

VALUES ( 'Data'[CustomerID] ),

LASTDATE ( 'Data'[TransactioDate] )

)
)
However in my data I have duplicate rows (used for other anaysis) such as transaction AC00. Using my measure above the total for AC00 is 20 where by i need it to be 10. I have tried using disctinct function but dont seem to arrive to solution.

How can I go about this?

The data is as follows

CustomerID	TransactionDate	Amount
AA11	1/01/2022	30
AA11	1/02/2022	40
AB01	1/02/2022	20
AC00	1/01/2022	10
AD01	1/03/2022	50
AA11	2/03/2022	100
AB01	2/03/2022	200
AC00	1/01/2022	10

 Desired Output AA11 is 100, AB01 200 , AC00 is 10 [Sum_LastTransactions] = 310

[CNENFRNL · Accepted solution]

Simple enough

[tamerj1]

Hi @Anonymous 
One way to do that

Sum_LastTransactions1 = 
VAR MaxDate = 
    MAXX ( 
        CALCULATETABLE ( 
            'Data', 
            ALLEXCEPT ( 'Data', 'Data'[CustomerID] )
        ),
    'Data'[TransactionDate]
    )
RETURN
    MAXX ( 
        FILTER ( 'Data', 'Data'[TransactionDate] = MaxDate ),
        Data[Amount]
    )