Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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'
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |