DAX measure not working correctly when two dates are the same

[jonclay]

Hi everyone

I'm using the following DAX measure to calculate the latest gift amount per customer:

LatestValue_Nett =

VAR last_date =

    CALCULATE ( MAX ( transaction[dateofpayment] ), transaction[dc1amount] + transaction[dc2amount] + transaction[dc3amount] <> BLANK () )

RETURN

    CALCULATE ( SELECTEDVALUE ( transaction[dc1amount] ), transaction[dateofpayment] = last_date )

This works fine until we have two transactions for the same person on the same day. In these cases nothing is shown (i.e. a blank value is shown). I assume this is because the DAX can't "decide" which transaction is the latest one (all our transactions have a timestamp of 00:00:00).

How can I change the DAX to show the highest value (dc1amount + dc2amount + dc3amount) if there are multiple transactions on the same day?

Many thanks
Jon

[jonclay]

Hi @DataInsights 

Thank you so much, that worked perfectly!

Best wishes
Jon

[DataInsights · Accepted solution]

@jonclay,

Try this measure:

LatestValue_Nett = 
// calculate total amount for each date/person
VAR vBaseTable =
    ADDCOLUMNS (
        'transaction',
        "@Amount",
            CALCULATE (
                SUM ( 'transaction'[dc1amount] ) + SUM ( 'transaction'[dc2amount] )
                    + SUM ( 'transaction'[dc3amount] )
            )
    )
// calculate max amount for each date/person
VAR vMaxTable =
    ADDCOLUMNS (
        vBaseTable,
        "@MaxAmount",
            VAR vDate = 'transaction'[dateofpayment]
            VAR vPerson = 'transaction'[person]
            RETURN
                MAXX (
                    FILTER (
                        vBaseTable,
                        'transaction'[dateofpayment] = vDate
                            && 'transaction'[person] = vPerson
                    ),
                    [@Amount]
                )
    )
// sum max amount for each date/person
VAR vResult =
    SUMX ( FILTER ( vMaxTable, [@Amount] = [@MaxAmount] ), [@Amount] )
RETURN
    vResult

Here's the result using the sample data below: