Forum Discussion
Retrieve last entry by group
Hi there,
I hope that you are able to help me with my problem.
In the example table below, I am trying to calculate Column D where I’d like to display the latest “Process Date” (Column C) for each “Project ID” (Column A).
In other words, all Project ID rows should display 19th of November 2018 in Column 4.
I tried LOOKUPVALUE and CALCULATE functions but haven’t been able to get this to work.
Any advice is most welcome!
| Project ID | Final Total | Process Date | Required Result |
| 1 | 1000 | 15/05/2018 | 19/11/2018 |
| 1 | 1500 | 27/09/2018 | 19/11/2018 |
| 1 | 2000 | 19/11/2018 | 19/11/2018 |
| 2 | 500 | 13/07/2018 | 21/09/2018 |
| 2 | 750 | 21/09/2018 | 21/09/2018 |
| 3 | 250 | 23/02/2018 | 25/12/2018 |
| 3 | 500 | 17/06/2018 | 25/12/2018 |
| 3 | 900 | 25/12/2018 | 25/12/2018 |
No worries. You can just add the filter on Category back in after the ALLEXCEPT
Final Date = CALCULATE ( MAX ( Project[Process Date] ), ALLEXCEPT ( Project, Project[Project ID] ), //Removes all contexts/filters except for Project ID Project[Category] = "Rock" //Adds back in a filter on category )
7 Replies
Try this:
Final Date = CALCULATE( MAX(Project[Process Date]), ALLEXCEPT(Project, Project[Project ID]) )Hope this helps
David
- Hi David,Thanks for the solution. It works exactly with the given scenario. Unfortunately for me I did not represent my problem correctly.
Actually there is another column that must be taken into account when calculation the results.Clarified problem statement:
Display latest "Process Date" where "Category" = Rock for each "Project ID".My apologies for missing this additional requirement earlier.Thank you so much for looking into this!Project ID Category Final Total Process Date Required Result 1 Rock 1000 15/05/2018 19/11/2018 1 Paper 1500 27/09/2018 19/11/2018 1 Rock 2000 19/11/2018 19/11/2018 2 Rock 500 13/07/2018 13/07/2018 2 Paper 750 21/09/2018 13/07/2018 3 Rock 250 23/02/2018 17/06/2018 3 Rock 500 17/06/2018 17/06/2018 3 Scissors 900 25/12/2018 17/06/2018 No worries. You can just add the filter on Category back in after the ALLEXCEPT
Final Date = CALCULATE ( MAX ( Project[Process Date] ), ALLEXCEPT ( Project, Project[Project ID] ), //Removes all contexts/filters except for Project ID Project[Category] = "Rock" //Adds back in a filter on category )
This type of thing is perfect for Power Query. Here's the final table I got:
You can see what I did in Power Query and the applied steps.
Here is the file:
Hi, the link to the workbook is invalid. Can you please share again?
Hi, the link to the workbook is invalid. Can you please share again?
