topic Re: Help with DAX code to group a column by an account ID and look for last payment date in DAX Commands and Tips

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

Anonymous — Fri, 28 Apr 2023 14:11:47 GMT

@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

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

FreemanZ — Fri, 28 Apr 2023 14:34:02 GMT

hi Anonymous

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

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

Anonymous — Fri, 28 Apr 2023 14:37:18 GMT

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.

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

Anonymous — Fri, 28 Apr 2023 14:38:19 GMT

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

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

FreemanZ — Fri, 28 Apr 2023 14:51:14 GMT

hi Anonymous

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:

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

Anonymous — Fri, 28 Apr 2023 15:11:54 GMT

FreemanZ You are very talented 🤔

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

Ajendra — Fri, 28 Apr 2023 15:30:30 GMT

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