earlier or lookup value any measure column that could help

[Anonymous]

I am trying to create the column "Final Completed" as the example below.  In this example i have three projects which have four steps.  In the Final Complete column i want to pull the date the project was completed for each of the projects.  If the date is blank at the 700 milestones which is the last step.  then i don't want it to populate for that project. 

I have tried Final Completed= Calculate(Max(Milestone), table1[Record ID]=earlier(table1[record ID])

the data just repeats itself, I tried lookupvalue as well and trying to add filters as well no luck.  Any help will be greatly appreciated.

Record ID	Description	Milestone	Milestone Completed	Final Completed
1	1st Step	200	2/5/2020	2/6/2020
1	2nd Step	400	2/6/2020	2/6/2020
1	3rd Step	600	2/6/2020	2/6/2020
1	4th Final Step	700	2/6/2020	2/6/2020
2	1st Step	200	2/6/2020
2	2nd Step	400	3/7/2020
2	3rd Step	600	7/7/2020
2	4th Final Step	700
3	1st Step	200	3/5/2020	5/23/2020
3	2nd Step	400	4/2/2020	5/23/2020
3	3rd Step	600	5/2/2020	5/23/2020
3	4th Final Step	700	5/23/2020	5/23/2020

[v-lili6-msft · Accepted solution]

hi   @Anonymous 

Please add a FILTER Funtion your formula and add a conditional that table1[Milestone]=700

https://docs.microsoft.com/en-us/dax/filter-function-dax

Final Completed =
CALCULATE (
    MAX ( table1[Milestone Completed] ),
    FILTER (
        table1,
        table1[Record ID] = EARLIER ( table1[record ID] )
            && table1[Milestone] = 700
    )
)

Result:

Regards,

Lin

[Anonymous]

Awesome!! thank you for quick reply everyone.  I am still trying to learn the EARLIER Function and when to use FILTER fucntion

[amitchandak]

Try

final calculated = maxx(FILTER(Sheet1,Sheet1[Description]="4th Final Step" && Sheet1[Record ID]=EARLIER(Sheet1[Record ID])),Sheet1[Milestone Completed])

Link: https://www.dropbox.com/s/m1c5minott5hf30/Milestone.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin