Forum Discussion
Solved
Sort a table before calculate based on date
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.
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 |
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.
3 Replies
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.