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.
Guys- have a question here.
I am trying to grab an Actual FinishDate value from each project and then reflect it in a new column/measure. In this example, for project ID 1000, I like to get the actual FinishDate from Phase Name [milestone 1] and insert it into Phase Name [Phase 1].
Then I will do that for project ID 2000 etc.
I will use the same logic for Phase 2, milestone 2 etc for subsequent projects.
Solved! Go to Solution.
Hi,
Please check the below picture.
It is for creating a new column.
Actual_Milestone CC =
VAR currentproject = Data[Project ID]
VAR currentphasenumber =
RIGHT ( Data[Phase Name], 1 )
VAR filter_only_milestone =
FILTER (
Data,
Data[Project ID] = currentproject
&& CONTAINSSTRING ( Data[Phase Name], "M" )
&& RIGHT ( Data[Phase Name], 1 ) = currentphasenumber
)
VAR milestonedate =
MAXX ( filter_only_milestone, Data[Actual Finishdate] )
RETURN
IF ( Data[Estimate Finishdate] = BLANK (), BLANK (), milestonedate )
Hey! @calvgo
The following Calculated Column works perfectly fine.
Hey! @calvgo
The following Calculated Column works perfectly fine.
Hi Shwet
what if data is changed to the following:
ProjectNamePhase NameForecastFinishDateActualFinishDateCalculated Value
1000 | Project Initiation Phase | 23/01/2018 | 5/02/2018 | 5/02/2018 |
1000 | M02XYZ | 00/00/0000 | 5/02/2018 | |
1000 | Scope and Feasibility Phase | 28/05/2018 | 12/07/2018 | 12/07/2018 |
1000 | M04AGD | 00/00/0000 | 12/07/2018 | |
1000 | Design Phase | 14/06/2019 | 11/06/2019 | 28/05/2019 |
1000 | M12ADS | 00/00/0000 | 28/05/2019 | |
1000 | Delivery Phase | 20/05/2020 | 27/08/2020 | 30/04/2020 |
1000 | M23AAA | 00/00/0000 | 30/04/2020 | |
1000 | Hand Over Phase | 10/12/2019 | 4/02/2021 | 4/02/2021 |
1000 | M24BBB | 00/00/0000 | 4/02/2021 | |
1000 | Project Close Phase | 10/12/2019 | 00/00/0000 | 4/02/2021 |
1000 | M25CCC | 00/00/0000 | 4/02/2021 | |
2000 | Delivery Phase | 29/01/2019 | 00/00/0000 | 00/00/0000 |
2000 | M23AAA | 00/00/0000 | 00/00/0000 | |
2000 | Hand Over Phase | 1/03/2019 | 00/00/0000 | 00/00/0000 |
2000 | M24BBB | 00/00/0000 | 00/00/0000 | |
2000 | Project Close Phase | 29/02/2020 | 00/00/0000 | 00/00/0000 |
2000 | M25CCC | 00/00/0000 | 00/00/0000 |
Hi,
Please check the below picture.
It is for creating a new column.
Actual_Milestone CC =
VAR currentproject = Data[Project ID]
VAR currentphasenumber =
RIGHT ( Data[Phase Name], 1 )
VAR filter_only_milestone =
FILTER (
Data,
Data[Project ID] = currentproject
&& CONTAINSSTRING ( Data[Phase Name], "M" )
&& RIGHT ( Data[Phase Name], 1 ) = currentphasenumber
)
VAR milestonedate =
MAXX ( filter_only_milestone, Data[Actual Finishdate] )
RETURN
IF ( Data[Estimate Finishdate] = BLANK (), BLANK (), milestonedate )
Hi JH,
Thanks for your post. What if data is changed to the below?
ProjectNamePhase NameForecastFinishDateActualFinishDateCalculated Value
1000 | Project Initiation Phase | 23/01/2018 | 5/02/2018 | 5/02/2018 |
1000 | M02XYZ | 00/00/0000 | 5/02/2018 | |
1000 | Scope and Feasibility Phase | 28/05/2018 | 12/07/2018 | 12/07/2018 |
1000 | M04AGD | 00/00/0000 | 12/07/2018 | |
1000 | Design Phase | 14/06/2019 | 11/06/2019 | 28/05/2019 |
1000 | M12ADS | 00/00/0000 | 28/05/2019 | |
1000 | Delivery Phase | 20/05/2020 | 27/08/2020 | 30/04/2020 |
1000 | M23AAA | 00/00/0000 | 30/04/2020 | |
1000 | Hand Over Phase | 10/12/2019 | 4/02/2021 | 4/02/2021 |
1000 | M24BBB | 00/00/0000 | 4/02/2021 | |
1000 | Project Close Phase | 10/12/2019 | 00/00/0000 | 4/02/2021 |
1000 | M25CCC | 00/00/0000 | 4/02/2021 | |
2000 | Delivery Phase | 29/01/2019 | 00/00/0000 | 00/00/0000 |
2000 | M23AAA | 00/00/0000 | 00/00/0000 | |
2000 | Hand Over Phase | 1/03/2019 | 00/00/0000 | 00/00/0000 |
2000 | M24BBB | 00/00/0000 | 00/00/0000 | |
2000 | Project Close Phase | 29/02/2020 | 00/00/0000 | 00/00/0000 |
2000 | M25CCC | 00/00/0000 | 00/00/0000 |
Hi @calvgo ,
Please provide some sample data in a text format and expected output.
You can follow the below post to get your answer quickly:-
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |