Selecting a value in a column in the same row as the max date in another column

[Lobby]

Hi All,

Thank you in advance for any support you can give! I have a data model with two tables, clients, projects, and reviews. Each client can have multiple projects, and each project can have multiple reviews. Within the review table, there are two dates: the planned date for the review and the actual date of the review. I am trying to create a straight table that shows client_id, project_id, the planned date of their latest review, and the actual date of their latest review. It is important to note that it is possible for the planned date field to be filled but the actual date to be null if the review hasn't actually happened yet. See below as an example.

Client_id	Project_id	planned_date	actual_date
1	A	12/12/2024	-
1	A	11/10/2024	02/01/2025
1	B	14/12/2024	03/01/2025
2	C	15/12/2024	-
2	C	16/12/2024	05/01/2025
3	D	17/12/2024	06/01/2025

When I make this table, I have the client_id and the project_id as dimensions in the table. I added the latest planned review date as a measure so I could use Max() to find the latest review. I would then like to populate the actual date column with the cell associated with that latest planned date. If the review hasn't happened yet, I want the actual date to return a null value as is in the data model.

I am unsure how to achieve this. I cannot use Max() again as if the review hasn't happened yet, this is null. I couldn't use Aggr either as, if I understand correctly, it provides an aggregation based on dimensions not a dimension based on the return of an aggregation. I am also trying set analysis but can't quite get it to work. Any help would be greatly appreciated!

[Chanty4u]

Try this 

Max(

  {<

    planned_date = {"=$(=Max(planned_date))"}

  >}

  actual_date

)

[Chanty4u]

Or may be 

Only(

  {<

    planned_date = {"=$(=Max(planned_date))"}

  >}

  actual_date

)

[marcus_sommer]

I'm not quite sure if I understand your use-case right but I would tend to do the essential work within the data-model and not with UI measures.

This could be mean to apply n aggregation like:

m: mapping load Client_id & '|' & Project_id, max(planned_date)
from X group by Client_id & '|' & Project_id; // optional where ...

and then using n applymap() to fetch the wanted data again.

Further helpful could be to use interrecord-functions like peek() and previous() within appropriate sorted resident-loads to create a running counter-information per key-value and/or offset-values between the dates and/or various other flag-information.

[Or]

This looks like a use case for FirstSortedValue(), which you can apply in a measure, once for each date field.