Help with DAX code to group a column by an account ID and look for last payment date

[EMUDATA]

@Community Members.

I need help in writing the appropriate DAX code that looks for the last payment date of each account in a dataset.

The code must group the Account ID column by Account IDs and look for the lastest date in Date of Payment column.

Account ID	Payment Method	Date of Payment
111000111	Cash	23/01/2023
111000111	Cash	24/01/2023
111000111	Cash	25/01/2023
111000111	Cheque	26/01/2023
111000111	Credit Card	27/01/2023
111000111	Direct Credit	28/01/2023
111000111	Cash	29/01/2023

[Ajendra]

Hey Dear,

You can try this code!

Output_Table = SUMMARIZE(FactTable,FactTable[ID],FactTable[Payment Mode],"Latest Date",

CALCULATE(MAX(FactTable[Payment Date]),FILTER(FactTable,FactTable[Payment Mode]=MAX(FactTable[Payment Mode]))))

Thanks,

Ajendra

[EMUDATA]

@FreemanZ something life IF(lastdate),1,0. But I am missing how to do this for each account id

[FreemanZ · Accepted solution]

hi @EMUDATA 

then try to add a calculated column like:

column = 
VAR _lastdate=
MAXX(
    FILTER(
        data,
        data[Account ID]=EARLIER(data[Account ID])
    ),
    data[Date of Payment]
)
RETURN
IF(
    [Date of Payment]=_lastdate,
    1,0
)

it worked like:

[EMUDATA]

@FreemanZ  You are very talented 🤔

[EMUDATA]

@FreemanZ thank you but I need to create a conditional column that indicates the last payment so I can filter data on last payment record for each account.

[FreemanZ]

hi @EMUDATA 

try to plot any visual with Account ID column and a measure like:
Measure = MAX(TableName[Date of Payment])