Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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 |
Solved! Go to 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]
)
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]
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
22 | |
17 | |
11 | |
11 | |
10 |
User | Count |
---|---|
31 | |
25 | |
15 | |
13 | |
12 |