How do I find the latest date related to an id.

[zingiwng]

I have 2 tables and I am trying to find and display the latest contact date for each opportunity

The tables are linked with with ID 1 -> * Oppt_ID

Opportinities

ID              Name             Account Manger

1                oppt 1            John Doe

2                oppt 2            Jane Doe

Activites

ID           Oppt_ID         Note                             Latest Contact

1              1                    sent email                     12/26/2016

2              1                    made phone call           02/20/2017

3              1                    on site meeting            04/28/2017

4              2                    first meeting                 01/03/2017

5              2                    email sent                     05/01/2017

I was thinking I could create a new column with a simple yes or no and filter by that, but I'm not sure how to aproach this.

I've tried following queries below but the column is always yes.

column = if(RELATED(Opportunities[id]) = [oppt_id] && MAX([Latest Contact]), "yes", "no")

column = if(RELATED(Opportunities[id]) = [oppt_id] && LASTDATE(Activities[Latest Contact]), "yes", "no")

What can I do to find the Latest Contact for an opportunity?

Thanks

zingwing

[Greg_Deckler · Accepted solution]

Column = CALCULATE(MAX([Latest Contact]),RELATEDTABLE(Activities))

In your Opportunities table create that column. Make sure Opportunities and Activities are related on ID in Opportunity and Oppt_ID in Activities.

[zingiwng]

Awesome thanks @Greg_Deckler  that query does that the job.

However, I found one issue. If I add the notes field from activites to the table it will still will display every note realated. 

ID            Opportunity           Latest Contact            Note

1              opp1                        04/28/2017                  on site meeting

1              opp1                        04/28/2017                  made phone call

1              opp1                        04/28/2017                  sent email

Is there a way to show the correct note with the latest contact?

[Greg_Deckler]

Perhaps create a measure for Notes like:

MeasureNote = VALUES([Note])

[Greg_Deckler]

Nope, that won't work, let me see if I can figure it out.