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!
Solved! Go to Solution.
@bmoon 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]
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
It worked. Thanks so much!
@bmoon 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]
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar