LASTDATE by Record ID

[Anonymous]

We're trying to find out the last time an Activity was created for each ID in a set of records.

Each Lead has an ID and, if contacted, various activities at different times like this:

I've tried the following measure but I get the last date of the year:

Lead Follow up = CALCULATE(LASTDATE('Leads Activities'[Activity Created].[Date]),ALLEXCEPT('Leads Activities','Leads Activities'[Lead ID]))

Am I missing something from this formula or do I need to use a different measure?   Would it be something to do with that some records don't have an Activity Created date at all?

I then wanted to display in a table one row per ID with Name, Record Owner, Activity Type/Date (when last contacted) and Days Since activity based on the Last Follow Up.

Would really appreciate any ideas.

Many thanks

[ankitpatira · Accepted solution]

@Anonymous In power bi desktop go to query editor (Edit Queries), right click your query and duplicate. Then to the duplicated query under Transform tab -> Group by as shown below. Finally Close&Apply and ensure there is active relationship between lead id columns of both the tables. Then use table visual and use leadid,activity from the duplicated columns and other columns from your original query and you will get the desired output.

[Anonymous]

That is awesome.  Thank you so much.