Your project is 62% complete. Where should it be today?

Microsoft Lists · Power Automate

A completion percentage becomes more useful when you compare it with the timeline. Here is the daily date-refresh pattern behind that comparison in Central Point.

Imagine a project reporting 62% complete. That number tells you something about the work delivered. It does not tell you whether the project is progressing at the pace the plan requires.

If 80% of the planned duration has elapsed, the gap is −18 percentage points. That deserves a conversation about the remaining work, dependencies, and delivery dates.

For this example, I call the solution Central Point. It uses Microsoft Lists and Power Automate to make this comparison visible. A small scheduled flow keeps a stored Today date current. Calculated columns use it to work out expected progress. This article focuses on refreshing the date and calculating expected progress. I will cover retrieving actual completion from Planner in a separate article.

Central Point list illustration with fictional projects, colored statuses, dates, and green actual-progress and purple expected-progress bars.
Central Point overview. Recreated from the inspected list layout with entirely fictional projects, customer labels, dates, and percentages. This is an illustration, not a production screenshot.

What expected progress actually measures

Expected progress = (Today − Start date) / (Finish date − Start date)
Progress gap = Actual progress − Expected progress

This is a linear calendar-time reference: it assumes progress should accumulate evenly between the chosen dates. It is useful for screening a portfolio and deciding where to ask questions. It does not account for task weighting, working calendars, or the critical path.

It also differs from Earned Value. Earned Value measures the budgeted value of work performed. Dates alone produce a time-based expected-progress indicator; they do not establish EV or a formal EVM schedule variance.

Fictional example using a 100-day duration
MeasureValue
Elapsed duration80 days
Expected progress80%
Actual progress62%
Progress gap−18 percentage points

The gap is a prompt to investigate. A project with substantial testing or deployment work near the end may follow a different planned curve. Before interpreting the result, agree how actual completion is measured and which dates define the comparison.

Close-up comparing 62 percent actual progress with 80 percent expected progress and an 18 percentage point gap.
Green bars show actual completion; purple bars show expected progress. The gap column is a suggested addition. All values are fictional.

Use the licenses you already have

If you already have SharePoint and Microsoft Lists through a Microsoft 365 or Office 365 plan that includes Power Automate rights for standard connectors, the solution shown here requires no additional license purchase. The scheduled Today refresh uses the standard SharePoint connector and built-in actions; it does not require Power Automate Premium.

This applies to the date-refresh and expected-progress solution in this article. Any future Planner or Dataverse integration needs its own licensing check.

Why a flow needs to refresh Today

The list contains a real Date and Time column called Today, configured to display the date only. Power Automate updates that field on each selected item.

This is different from putting TODAY() into a calculated-column formula. Microsoft documents that TODAY is unsupported in calculated columns, although it is supported in column defaults. A default supplies an initial value; it is not a daily refresh mechanism.

Daily scheduleStart the refresh at the agreed business time.
Current dateCalculate the date once for the run.
List updatesWrite Today into the selected project items.
Calculated columnsRecalculate the time-based reference.

The Today flow in Central Point

The flow Refresh Today (the name used in this example) uses six steps:

  1. Recurrence: every day at 1:00 a.m., with the Brasília time zone selected.
  2. Initialize variable: create the String variable var_Today.
  3. Get items: retrieve projects from the Central Point list.
  4. List Name: a Compose action holds the destination list name.
  5. Apply to each: process the returned list items.
  6. Update item: use each item’s ID and write only the Today property in the supplied item object.

The current date expression is:

formatDateTime(utcNow(),'MM-dd-yyyy')

The flow retrieves items with pagination enabled and a threshold of 2,000. That setting should match the portfolio’s size and be monitored as it grows. Its most recent inspected run completed successfully, but a successful run still needs checks for coverage and data quality.

A clearer date expression for a reusable example

For a new implementation, I would make the reporting time zone explicit and use an ISO date format:

convertTimeZone(utcNow(),'UTC','E. South America Standard Time','yyyy-MM-dd')

This is a proposed improvement, not the expression currently saved in the flow. At the existing 1:00 a.m. Brasília schedule, UTC and Brasília fall on the same calendar date. Making the conversion explicit protects the intended date if the schedule changes or someone runs the flow near a UTC date boundary. Verify how the saved date reads back under your SharePoint regional settings.

The columns behind the calculation

The operational calculation uses a date-to-number helper chain. These are the formulas currently configured in the list:

Calculated columnFormula
Today in Number=Today
OPS Start Estimated Date in Number=[OPS Start Date]
OPS Finish Estimated Date in Number=[OPS Finish Date]
OPS Effort in Days=[OPS Finish Estimated Date in Number]-[OPS Start Estimated Date in Number]

The final % OPS EP column returns a Number displayed as a percentage:

=IFERROR(([Today in Number]-[OPS Start Estimated Date in Number])/[OPS Effort in Days],0)

The column called “Effort in Days” is actually elapsed calendar duration. It does not measure person-days or working-day effort. Keeping that distinction clear prevents misleading comparisons.

Handle invalid dates separately from 0% progress

The current formula can return negative progress before the start and more than 100% after the finish. It also turns calculation errors into 0. Those behaviors need explicit decisions before reusing the pattern.

For a beginner-facing implementation, my preferred rules are: show no progress result when dates are missing or finish is not after start; otherwise limit expected progress to 0–100%. Track overdue days separately, so the capped progress value does not hide lateness.

Missing dates or Finish ≤ Start → flag the dates for review
Today ≤ Start                  → 0%
Today ≥ Finish                 → 100%
Otherwise                      → (Today − Start) / (Finish − Start)

These are proposed rules. Validate them in a test list before changing an established report. A same-day milestone needs its own policy because its duration is zero. Also choose whether your reporting convention represents the start or end of a day.

Illustrative date checks for future starts, overdue projects, and missing finish dates.
Date checks for a reusable example. Warning colors and date-health labels illustrate proposed guardrails, rather than a verified production configuration. All values are fictional.

What I would check before rolling this out

  • Exact status values: use the list’s current labels in the Get items filter. Old labels can silently include projects you meant to exclude.
  • Freshness: display the reporting date and the last successful project-data refresh. New dates with stale actual completion can create a misleading gap.
  • Coverage: check pagination and confirm every eligible project was updated.
  • Update side effects: date refreshes can affect item versions, Modified metadata, and flows triggered by item changes.
  • Boundary cases: test missing dates, future starts, same-day milestones, reversed dates, overdue work, and completed projects.

The automation supports a more specific portfolio discussion: which projects need attention, which dates need correction, and what must happen next to protect delivery. The indicator is useful when those follow-up decisions are clear.

Build this capability in your PMO

Want your team to build and maintain automations like this? Contact me about practical Power Automate training, or consulting hours to review your project data, formulas, and flow design.

Discuss your PMO automation needs →

Further reading

Fernando Sampaio · Shift2Modern · Practical automation for project and portfolio management

Leave a Comment

Your email address will not be published. Required fields are marked *