Step 1: Schedule the flow
Use Recurrence trigger:
Frequency: Weekly
Interval: 1
On these days: Wednesday
Time: set your desired time
Step 2: List rows from your Excel table (tasks)
Use Excel Online (Business) → List rows present in a table
Connect to the Excel file & table that contains your tasks
(Optional) If your Excel is in OneDrive or SharePoint, select the location & document library accordingly
Step 3: Filter rows to only completed tasks in the last 30 days
Since Power Automate’s Excel connector doesn’t support complex OData filters on dates, filter in the flow:
Add a Filter array action
From: value output of the previous Excel action (all rows)
Condition:
and(
not(empty(item()?['Completed Date'])),
greaterOrEquals(formatDateTime(item()?['Completed Date'], 'yyyy-MM-dd'), formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-dd'))
)
This filters rows where:
Completed Date is not empty
Completed Date is within the last 30 days
Step 4: Calculate average completion time (days)
Add Initialize variable (name: totalDays, type: Integer, value: 0)
Add Initialize variable (name: taskCount, type: Integer, value: 0)
Add an Apply to each — loop through filtered array from Step 3
Inside loop:
Calculate days between Start date and Completed Date for each task:
sub(
ticks(item()?['Completed Date']),
ticks(item()?['Start date'])
)
This gives ticks, so divide by number of ticks in a day:
Number of ticks in a day = 864000000000
So days =
div(sub(ticks(item()?['Completed Date']),ticks(item()?['Start date'])),864000000000)
Add to totalDays variable:
Use Set variable or Increment variable action:
totalDays = totalDays + daysDifference
Increment taskCount by 1
After the loop, calculate average:
Initialize variable currentAverage (Float)
Use Set variable with expression:
if(equals(variables('taskCount'), 0), 0, div(variables('totalDays'), variables('taskCount')))
Step 5: Read the current Prev. Average and Current Average values from the "summary" Excel table
Use Excel Online (Business) → List rows present in a table on the summary table that has columns: Prev. Average, Current Average, Trend
Since it only has 1 row, get that row's values:
prevAverage = float(item()?['Prev. Average'])
currentAverageOld = float(item()?['Current Average'])
Step 6: Update the summary table row
Use Excel Online (Business) → Update a row action
For the single row in summary table:
Set Prev. Average = currentAverageOld (the current average before update)
Set Current Average = currentAverage (calculated in Step 4)
Set Trend = currentAverage - currentAverageOld
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards,
Riyaz
