Get the latest date based on reference ID

[Monns]

I am basically trying to get the number of days between the latest date from the reference table and the current date. I thought MAX was supposed to get me the latest date through the last id match but I only get the first one.

This is my formula:
CONCATENATE(HOUR (Today() - MAX(SELECT(Contact_txn[Date], [id] = [ContactRec])))/24, " Days Ago")

The concatenation of all these text values (
…(HOUR(
…(TODAY() - MAX(
…The list of values of column ‘Date’
…from rows of table ‘CONTACT_TXN’
…where this condition is true: ((The value of column ‘id’) is equal to (The value of column ‘ContactRec’)))) / 24
…" Days Ago"

I am not sure what I am missing. Any help would be appreciated.

[Steve]

What is this supposed to be doing?

SELECT(Contact_txn[Date], [id] = [ContactRec])

[Monns]

I believe it is supposed to grab the date(From Table B) on the row that has the id value(From Table A) matching ContactRec value (Ref on Table B)

[Steve · Accepted solution]

Try this instead:

SELECT(Contact_txn[Date], [_THISROW].[id] = [ContactRec])

[Monns]

Worked like a charm, thank you!