Forum Discussion
Solved
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...
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 )
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.
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".
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 )Hi David,
Thank you very much for the solution! It works exactly as required.