Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |