cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmoon
Frequent Visitor

Help Identifying Last Amount by ID and Date in Related table

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:

bmoon_0-1637696634264.png

This is how the tables are related:

bmoon_1-1637696864938.png

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_IDCurrent_Pymt_Dt = Last_Pymt_Rqst_Dt ),
        SUM ( 'Pymt rqst'[CADA_Net_Expn_Dol] ),
        0
    )

Please help!

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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]
)

 

smpa01_0-1637699835868.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

2 REPLIES 2
bmoon
Frequent Visitor

It worked. Thanks so much!

smpa01
Super User
Super User

@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]
)

 

smpa01_0-1637699835868.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors