Check for previous year entry

Hi Anonymous ,

I have created a sample for your reference, please check the following steps as below.

1. add a custom column as below.

= Table.AddColumn(#"Changed Type", "Custom", each [Year]+1)

2. Self merge the table like that.

3. Expand the Eval Score column and add a custom column.

= Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y")

4. Then we can remove unnecessary columns and get the excepted result.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDsAgCATAv3D2UEWgvsXw/2/UWJrgpgcOTjaszEntqkqF6homLxssoH9wI8h6tD0BGiA5wZg4wAI4t3BuGX+JnmEEHB8TrBWsFbxWsUVxqeWEBvS8413s/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Employee ID" = _t, #"Eval Score" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Employee ID", Int64.Type}, {"Eval Score", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Year]+1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Year", "Employee ID"}, #"Added Custom", {"Custom", "Employee ID"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Eval Score"}, {"Added Custom.Eval Score"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Custom",{{"Employee ID", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Added Custom.Eval Score] = null then "N" else "Y"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom.Eval Score", "Custom"})
in
    #"Removed Columns"

Alternatively, We can achieve that by dax.

Column = 
VAR minyear =
    CALCULATE ( MIN ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Employee ID] ) )
RETURN
    IF ( 'Table'[Year] = minyear, "N", "Y" )

Pbix as attached.