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||Next Event||Next Stage|
Solved! Go to Solution.
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] ) )
@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'