Forum Discussion
To add a stage column
Thank you for your help.
I would like to refer to table T_Reference and change table T_From to table T_To.
(I want to add a stage column)
Is it possible with DAX or PowerQuery?
The sample file is this.
T_Reference
| Project | Milestone | Date | Stage |
| PJ_A | MS1 | 02/01/2021 | A |
| PJ_A | MS2 | 08/01/2021 | B |
| PJ_A | MS3 | 02/01/2022 | C |
| PJ_B | MS1 | 02/01/2022 | A |
| PJ_A | MS4 | 08/01/2022 | D |
| PJ_B | MS2 | 08/01/2022 | B |
| PJ_B | MS3 | 02/01/2023 | C |
| PJ_B | MS4 | 08/01/2023 | D |
| PJ_A | 01/30/2024 | E | |
| PJ_B | 01/30/2024 | E |
T_From
| Date | Cost | Project |
| 12/01/2020 | 170000 | PJ_A |
| 02/01/2021 | 7813 | PJ_A |
| 05/01/2021 | 782000 | PJ_A |
| 08/01/2021 | 9375 | PJ_A |
| 12/01/2021 | 175000 | PJ_A |
| 12/01/2021 | 37000 | PJ_B |
| 02/01/2022 | 31250 | PJ_A |
| 02/01/2022 | 18750 | PJ_B |
| 05/02/2022 | 51000 | PJ_A |
| 05/02/2022 | 23000 | PJ_B |
| 08/01/2022 | 3125 | PJ_A |
| 08/01/2022 | 41667 | PJ_B |
| 12/01/2022 | 413000 | PJ_A |
| 12/01/2022 | 451000 | PJ_B |
| 01/30/2024 | 25000 | PJ_A |
| 02/01/2023 | 12500 | PJ_B |
| 05/01/2023 | 1707000 | PJ_B |
| 08/01/2023 | 16667 | PJ_B |
| 12/01/2023 | 188000 | PJ_B |
| 01/30/2024 | 12500 | PJ_B |
T_To
| Date | Cost | Project | Stage |
| 12/01/2020 | 170000 | PJ_A | A |
| 02/01/2021 | 7813 | PJ_A | A |
| 05/01/2021 | 782000 | PJ_A | B |
| 08/01/2021 | 9375 | PJ_A | B |
| 12/01/2021 | 175000 | PJ_A | C |
| 12/01/2021 | 37000 | PJ_B | A |
| 02/01/2022 | 31250 | PJ_A | C |
| 02/01/2022 | 18750 | PJ_B | A |
| 05/02/2022 | 51000 | PJ_A | D |
| 05/02/2022 | 23000 | PJ_B | B |
| 08/01/2022 | 3125 | PJ_A | D |
| 08/01/2022 | 41667 | PJ_B | B |
| 12/01/2022 | 413000 | PJ_A | E |
| 12/01/2022 | 451000 | PJ_B | C |
| 01/30/2024 | 25000 | PJ_A | E |
| 02/01/2023 | 12500 | PJ_B | C |
| 05/01/2023 | 1707000 | PJ_B | D |
| 08/01/2023 | 16667 | PJ_B | D |
| 12/01/2023 | 188000 | PJ_B | E |
| 01/30/2024 | 12500 | PJ_B | E |
Hi icrmzgc ,
Here are the steps you can follow：
1. Create calculated column.
Stag = MINX( FILTER(ALL(T_Reference), 'T_Reference'[Date]>=EARLIER('T_From'[Date])&&'T_Reference'[Project]=EARLIER('T_From'[Project])),'T_Reference'[Stage])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi icrmzgc , you need to define the join (relationship) between T_Reference and T_From table. You have project codes with different milestones and dates. Your T_From table just have Project codes. I used the Project codes and the Date combo in Power Query to get what you might really need. See the files in my shared drive link below.
If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/Hello amustafa.
Thank you for your answer and sample file.
I didn't know that you could specify two parameters with Table.NestedJoin, so I learned a lot.However, unfortunately, some of the ideas I received are not able to set the stage.
I also want to set the Stage for the 10 items in red.Hi icrmzgc ,
Here are the steps you can follow：
1. Create calculated column.
Stag = MINX( FILTER(ALL(T_Reference), 'T_Reference'[Date]>=EARLIER('T_From'[Date])&&'T_Reference'[Project]=EARLIER('T_From'[Project])),'T_Reference'[Stage])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello, Liu Yang.
Thank you for your reply and file.
It's what I was looking for.
I don't fully understand the DAX style description with my current knowledge, but I would like to be able to understand it.
I'm really thankful to you.
