Using lookupvalue within summarize

[Anonymous]

I have a set of of data that has a list of projects and the dates at which they hit set milestones - I want to be able to understand which milestone is next for each project. I have generated a sample data set below to illustrate. 

I believe the code should be close to below, however to add corresponding stage column I am required to do a lookup from a result generated in the summary, which DAX doesn't allow.

Summary= ADDCOLUMNS( 
                SUMMARIZE(Table,'Table'[Project]),
                "Next Milestone Date", CALCULATE(MIN('Table'[Event Date], 'Table'[Status]="U")))

Status: C = Complete, U = Uncomplete

Project	Stage	Event Date	Status
Project A	1	01/09/2018	C
Project A	2	01/11/2018	C
Project A	3	01/05/2019	U
Project A	4	01/08/2019	U
Project A	5	01/01/2020	U
Project A	6	01/05/2020	U
Project B	1	01/01/2019	C
Project B	2	01/02/2019	U
Project B	3	01/08/2019	U
Project B	4	01/01/2020	U
Project B	5	01/02/2020	U
Project B	6	01/07/2020	U
Project C	1	01/10/2018	C
Project C	2	01/12/2018	C
Project C	3	01/01/2019	C
Project C	4	01/02/2019	U
Project C	5	01/05/2019	U
Project C	6	01/07/2019	U

Project	Next Event	Next Stage
Project A	01/05/2019	3
Project B	01/02/2019	2
Project C	01/02/2019	4

[AlB]

Hi @Anonymous 

Try this:

Summary =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table'; 'Table'[Project] );
        "Next Milestone Date"; CALCULATE ( MIN ( 'Table'[Event Date] ); 'Table'[Status] = "U" )
    );
    "Next Stage"; LOOKUPVALUE (
        'Table'[Stage];
        'Table'[Event Date]; [Next Milestone Date];
        'Table'[Project]; [Project]
    )
)

[Anonymous]

@AlB Thanks for the response.

Although I agree/understand the logic. Your solution generates the error, "a table of multiple values was supplier where a single value was expected'

[AlB · Accepted solution]

Works fine on my end. Check the file out