Forum Discussion

jonclay's avatar
jonclay
Icon for Helper IV rankHelper IV
3 years ago
Solved

DAX measure not working correctly when two dates are the same

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
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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:

     

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    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: