Retrieve last entry by group

[chengo555]

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

[Anonymous]

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:

https://1drv.ms/u/s!AoQIGRpzoxRH-j2KMwlHc95qrZbC

[FerdieBotha]

Hi, the link to the workbook is invalid. Can you please share again?

[FerdieBotha]

Hi, the link to the workbook is invalid. Can you please share again?

[dedelman_clng]

Try this:

Final Date = 
 CALCULATE(
     MAX(Project[Process Date]),
     ALLEXCEPT(Project, Project[Project ID])
)

Hope this helps

David

[chengo555]

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

[dedelman_clng · Accepted solution]

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
)

[chengo555]

Hi David,

Thank you very much for the solution! It works exactly as required.