topic Re: Help Identifying Last Amount by ID and Date in Related table in DAX Commands and Tips

Help Identifying Last Amount by ID and Date in Related table

Anonymous — Tue, 23 Nov 2021 19:49:34 GMT

I need help identifying the last amount for a grant ID. My data can have multiple transactions on the same date and so I've been trying to find the value using the max of a transaction ID and date. I've found many similar problems on the forum, but I haven't been able to get anything to work. Here's an example of my data and what I'm looking for:

This is how the tables are related:

My current DAX is, but it keeps returning 0:

CADA_Last_Rqst_Ind =
VAR Current_Pymt_Dt =
    SELECTEDVALUE ( 'Pymt tran'[PYMT_RQST_DATE] )
VAR Current_Tran_ID =
    SELECTEDVALUE ( 'Pymt rqst'[PYMT_TRAN_ID] )
VAR Last_Pymt_Rqst_Dt =
    CALCULATE ( MAX ( 'Pymt tran'[PYMT_RQST_DATE] ), 'Pymt rqst'[AWD_ID] )
VAR Last_Tran_ID =
    CALCULATE ( MAX ( 'Pymt rqst'[PYMT_TRAN_ID] ), 'Pymt rqst'[AWD_ID] )
RETURN
    IF (
        AND ( Current_Tran_ID = Last_Tran_ID, Current_Pymt_Dt = Last_Pymt_Rqst_Dt ),
        SUM ( 'Pymt rqst'[CADA_Net_Expn_Dol] ),
        0
    )

Please help!

Re: Help Identifying Last Amount by ID and Date in Related table

smpa01 — Tue, 23 Nov 2021 20:38:09 GMT

Anonymous you can use two measures like this

LastDisb = CALCULATE ( MAX ( 'Pymt Amt'[Ttl Disb] ), FILTER ( 'Pymt Amt', 'Pymt Amt'[Tran ID] = CALCULATE ( MAX ( 'Pymt Tran'[Tran ID] ), FILTER ( ALL ( 'Pymt Tran' ), 'Pymt Tran'[Entitiy ID] = MAX ( 'Pymt Tran'[Entitiy ID] ) && 'Pymt Tran'[Pymt Dt] = CALCULATE ( MAX ( 'Pymt Tran'[Pymt Dt] ), ALLEXCEPT ( 'Pymt Tran', 'Pymt Tran'[Entitiy ID] ) ) ) ) ) )

Date = MAXX ( ADDCOLUMNS ( 'Pymt Amt', "date", MAXX ( RELATEDTABLE ( 'Pymt Tran' ), 'Pymt Tran'[Pymt Dt] ) ), [date] )

Re: Help Identifying Last Amount by ID and Date in Related table

Anonymous — Tue, 23 Nov 2021 21:17:44 GMT

It worked. Thanks so much!