How to return the latest status per Project based on date

[#1]

Hi

I have a list of Projects with the status of each project at a certain point in time. What I would like to do is create a straight table that shows the latest status per project, ie something like:

Thanks for you help

Sample data:

Load

*

INLINE [

    ProjectID, StatusDate, Status

    P1, 2012/01/01, Active

    P1, 2012/01/20, on Hold

    P1, 2012/02/15, Discontinued

    P2, 2012/01/05, Active

    P2, 2012/01/07, on Hold

    P3, 2012/01/02, Active

    P3, 2012/01/09, Discontinued

];

[Gysbert_Wassenaar · Accepted solution]

Use ProjectID as dimension and create two expressions:

aggr(firstsortedvalue(StatusDate, -StatusDate),ProjectID)

aggr(firstsortedvalue(Status, -StatusDate),ProjectID)

[#3]

Thanks!