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.
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 |
Solved! Go to Solution.
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] ) )
@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'
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |