How to Calculate the Maximum Related Date in a SharePoint Projects List
Question details
The user needs a way to calculate and display the maximum date from related items in a Tasks list within a parent Projects list.

- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Managing a SharePoint Projects list linked to a Tasks list, where the user wants to identify and display the latest task deadline or date for each project.
- Observed behavior
- SharePoint cannot natively calculate the maximum date from related items in a Tasks list through a standard lookup or calculated column.
Ensure you have the necessary site permissions to create and edit Microsoft Power Automate flows, and that your Tasks list contains a lookup column correctly mapped to the Projects list.
Use Power Automate to Fetch and Calculate the Maximum Date
Since SharePoint lists lack native cross-list aggregate calculations, you can use a Power Automate flow to find the latest task date and write it back to the parent project item.
This solution relies on an automated flow that triggers whenever a task is created or updated. It queries the Tasks list for items related to the specific project, sorts them by date to find the maximum value, and updates a dedicated date column in the Projects list.
Navigate to your SharePoint Projects list. Click 'Add column', select 'Date and time', name it 'Latest Task Date' (or similar), and save it. This column will store your maximum date.
Open Microsoft Power Automate. Click 'Create', select 'Automated cloud flow', and choose the trigger 'When an item is created or modified' connected to your SharePoint Tasks list.
Add a new action and search for 'Get items' (SharePoint). Set the Site Address and List Name to your Tasks list. In the 'Filter Query' field, enter an OData query to match the Project ID (e.g., ProjectLookupId eq triggerOutputs()?['body/ProjectLookupId']).
In the same 'Get items' action, click 'Show advanced options'. In the 'Order By' field, type your task date column's internal name followed by 'desc' (e.g., TaskDate desc). Set the 'Top Count' field to 1. This ensures the flow only retrieves the single task with the latest date.
Add an 'Update item' action for the Projects list. Map the 'Id' to the Project Lookup ID from your trigger. Click into the new 'Latest Task Date' column field and select the date value returned from your 'Get items' step.

Try WPS Office for Seamless Document Management
While complex SharePoint list relationships require specialized Microsoft tools, WPS Office is an excellent, free alternative for your everyday document, spreadsheet, and presentation tasks. It offers a familiar interface and seamless compatibility with standard Office files.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installer compatible with your operating system.
- 2. Install the Software: Run the downloaded installation file and follow the straightforward on-screen instructions to set it up.
- 3. Open Your Project Files: Launch WPS Office and directly open, edit, and save your existing Office files without worrying about formatting issues.

Frequently Asked Questions
Can I use a SharePoint Calculated Column to find the max date from a lookup?
No, SharePoint calculated columns cannot reference cross-list lookup columns or perform aggregate functions (like MAX or SUM) across multiple related items.
Is there an out-of-the-box SharePoint alternative without using Power Automate?
Without Power Automate, the only other native alternatives require using third-party add-ins or developing custom SharePoint Framework (SPFx) web parts, as out-of-the-box lists do not support relational aggregations natively.
How quickly does the Power Automate flow update the parent Projects list?
If your flow uses the 'When an item is created or modified' trigger, the Projects list will typically update within seconds to a few minutes after a related task is saved, depending on the current Microsoft server load.




