topic Re: Sort a table before calculate based on date in DAX Commands and Tips

Sort a table before calculate based on date

luckygirl — Fri, 17 Jan 2025 02:39:51 GMT

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

Re: Sort a table before calculate based on date

Irwan — Fri, 17 Jan 2025 03:17:21 GMT

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.

Re: Sort a table before calculate based on date

luckygirl — Fri, 17 Jan 2025 03:36:47 GMT

Irwan Thanks a lot. Works well 🙂

Re: Sort a table before calculate based on date

Irwan — Fri, 17 Jan 2025 05:58:36 GMT

Hello luckygirl

Glad to be a help.

Thank you.