SharePoint Document Issues

How to Calculate the Maximum Related Date in a SharePoint Projects List

Jason MillerJason Miller Nov 3, 2026 869 views

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.

How to Calculate the Maximum Related Date in a SharePoint 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a Target Date Column

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.

2
Create a New Automated Flow

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.

3
Get Related Tasks

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']).

4
Sort to Find the Maximum Date

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.

5
Update the Projects List

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.

Use Power Automate to Fetch and Calculate the Maximum Date
Optimize Flow Performance: By using the 'Order By' and 'Top Count: 1' parameters, you eliminate the need for complex variables or looping arrays, making your flow run faster and more efficiently.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS Office website and download the free installer compatible with your operating system.
  2. 2. Install the Software: Run the downloaded installation file and follow the straightforward on-screen instructions to set it up.
  3. 3. Open Your Project Files: Launch WPS Office and directly open, edit, and save your existing Office files without worrying about formatting issues.
Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Lightweight suite that installs quickly and runs smoothly on both modern and older devices.Rich library of free project management and task tracking spreadsheet templates.Built-in PDF reader and editor to easily manage and share your final project reports.
microsoft office alternative - wps office

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.