Finding max value per id in a stacked data table

[forrestgump]

Hi there,

I have 2 tables. 1 called 'HeadcountTble' the other called 'SurveyTble'. The 'HeadcountTable' is normalized and has unique ids per person. The SurveyTble has multiple rows per person. The tables are linked 1 to many from the 'HeadcountTble' to the 'SurveyTble'. What i want to do is extract the max Report date per person in the survey table and enter it into a column in the 'HeadcountTble' or create a measure to be able to do this, or both if possible (As a measure and a calculated column).

I have tried as a calculated column for now 'Report Date = MAX(SurveyTble[Report date])' however this gives the maximum value over the whole table. I am guessing I need to filter the table first by the id and then find the max Report date from that table???

I will keep trying but any help would be greatly appreciated.

Kind regards,

Forrestgump

 

[forrestgump]

I think I have found the answer I can just use LastDate(SurveyTble[Report Date]) as either a column or a measure.

 

[forrestgump]

So I have discovered it works great as a calculated column in my headcount table however not quite as a measure. I think there will be a clever way of writing as a measure though! Any ideas?