Sort a table before calculate based on date

[luckygirl]

I have a table like below.

Code	CompletedDate	ProductDetail
1	1/01/2018	A
1	1/04/2019	B
1	23/05/2021	C
2	9/02/2018	D
2	5/05/2021	C
2	6/05/2022	A
3	1/01/2018	K
3	10/05/2019	B
3	30/06/2021	C
3	1/01/2024	L
4	6/06/2017	B
4	9/03/2018	J
4	5/05/2021	C
4	3/03/2024	D

I need to create a Target column based on CompletedDate for Each Code group. So the expected solution would be like below.

Code	CompletedDate	ProductDetail	Target
1	1/01/2018	A	B
1	1/04/2019	B	C
1	23/05/2021	C
2	9/02/2018	D	C
2	5/05/2021	C	A
2	6/05/2022	A
3	1/01/2018	K	B
3	10/05/2019	B	C
3	30/06/2021	C	L
3	1/01/2024	L
4	6/06/2017	B	J
4	9/03/2018	J	C
4	5/05/2021	C	D
4	3/03/2024	D

I tried the below DAX code. It generates correct results for some but not all. Can someone please help me. Thanks.

Target =

CALCULATE (

    MIN ( Tbl[ProductDetail] ),

    FILTER (

        'Tbl',

        Tbl[ClientCode] = EARLIER ( Tbl[ClientCode] )

         && Tbl[CompletedDate] > EARLIER ( Tbl[CompletedDate] )

    )

)

The results by the above code. Incorrect ones are highlighted in red.

Code	CompletedDate	ProductDetail	Target
1	1/01/2018	A	B
1	1/04/2019	B	C
1	23/05/2021	C
2	9/02/2018	D	A
2	5/05/2021	C	A
2	6/05/2022	A
3	1/01/2018	K	B
3	10/05/2019	B	C
3	30/06/2021	C	L
3	1/01/2024	L
4	6/06/2017	B	C
4	9/03/2018	J	C
4	5/05/2021	C	D
4	3/03/2024	D

[luckygirl]

@Irwan Thanks a lot. Works well 🙂

[Irwan]

Hello @luckygirl 

Glad to be a help.

Thank you.

[Irwan · Accepted solution]

hello @luckygirl 

please check if this accomodate your need.

create a new calculated column with following DAX

Target = 
var _Min = 
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]>EARLIER('Table'[CompletedDate])&&
        'Table'[Code]=EARLIER('Table'[Code])
    ),
    'Table'[CompletedDate]
)
Return
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]=_Min
    ),
    'Table'[ProductDetail]
)

Hope this will help.

Thank you.