Creating a custom column to identify if item is most current based on date and criteria

[ColetonC]

I have a table that lists a number of assessments with client, location, and date. Each client and location has multiple assessements performed. I would like to create a custom column that returns a "yes" if the particular assessment for that client and location is the most recent.

Below is an example of the data and what I am trying to accomplish. 

Current data:

Assessment Name	Client	Location	Date
Assessment	A	1	07/02/2021
Assessment	A	1	10/01/2022
Assessment	A	2	12/02/2022
Assessment	A	2	05/02/2023
Assessment	B	1	04/14/2023
Assessment	B	1	05/15/2024
Assessment	B	2	08/20/2023
Assessment	B	2	09/25/204

Desired outcome

Assessment Name	Client	Location	Date	Most Recent
Assessment	A	1	07/02/2021
Assessment	A	1	10/01/2022	Yes
Assessment	A	2	12/02/2022
Assessment	A	2	05/02/2023	Yes
Assessment	B	1	04/14/2023
Assessment	B	1	05/15/2024	Yes
Assessment	B	2	08/20/2023	Yes

[Sahir_Maharaj · Accepted solution]

Hello @ColetonC,

Can you please try the following approach:

Most Recent = 
IF(
    'Table'[Date] = 
        CALCULATE(
            MAX('Table'[Date]),
            ALLEXCEPT('Table', 'Table'[Client], 'Table'[Location])
        ),
    "Yes",
    BLANK()
)

[Sahir_Maharaj · Accepted solution]

Hello @ColetonC,

Can you please try the following approach:

Most Recent = 
IF(
    'Table'[Date] = 
        CALCULATE(
            MAX('Table'[Date]),
            ALLEXCEPT('Table', 'Table'[Client], 'Table'[Location])
        ),
    "Yes",
    BLANK()
)

[ColetonC]

Thank you for the response! Can this logic be applied to a custom column on the PowerQuery side when the data is being loaded in?

[Anonymous · Accepted solution]

Hi @ColetonC 

Thank you very much Sahir_Maharaj for your prompt reply.

If you want to customize the columns, try:

let
    Source = ...,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Assessment Name", type text}, {"Client", type text}, {"Location", Int64.Type}, {"Date", type date}}),
    // Add a custom column
    AddCustomColumn = Table.AddColumn(#"Changed Type", "Most Recent", each 
        if [Date] = List.Max(
            Table.SelectRows(#"Changed Type", 
                (x) => x[Client] = [Client] and x[Location] = [Location])[Date]) 
        then "yes" else "")

in
    AddCustomColumn

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.