Monthly COE ROI Summary Generator

Overview

FlowLibs - Monthly COE ROI Summary Generator is a scheduled cloud flow that runs on the first business day of each month and compiles a leadership-ready ROI report for the Power Platform Center of Excellence (COE). It reads the COE's analytics tables in Dataverse (flow run history, app inventory, maker registry, and a per-process avg-manual-time lookup), aggregates the prior month into a handful of high-signal KPIs — total flow runs, estimated hours saved, error rate, active makers, and new apps deployed — and writes a populated row into a templated Excel ROI tracker stored in SharePoint. The current-month vs prior-month deltas are baked into the email body as trend arrows so executives can read the story without opening the workbook.

The flow is built around the "Dataverse aggregate -> Excel template -> Executive email" reporting pattern: every metric source is an environment variable, every threshold (error rate red/yellow/green) is driven by a config row in Dataverse, and the recipient list is a single env var so the same solution promotes cleanly through Dev / Test / Prod environments.

Use Case

Most COE teams build their monthly ROI deck by hand: someone exports run history from the COE Starter Kit, pivots it in Excel, looks up avg-manual-time per process from a spreadsheet, formats trend arrows, and emails the result to the leadership team. This flow removes that recurring 4-6 hour effort entirely. The COE owns the inputs (the avg-manual-time table and the active-maker tagging in Dataverse); the flow owns the math, the formatting, and the delivery. It is a good fit for organizations that have already deployed the Microsoft COE Starter Kit (or an equivalent Dataverse-based usage tracker) and want a defensible monthly ROI artifact in front of executives without anyone having to remember to build it.

The flow is ideal for teams that:

• Power Platform COE leads who need a recurring, defensible monthly ROI artifact for executive sponsors.
• IT admins running the COE Starter Kit who want the run-history and maker-inventory tables to drive an executive summary automatically.
• Finance / business-ops partners who need an hours-saved estimate they can plug into a yearly automation business case.
• Organizations standing up a fresh COE that want an out-of-the-box ROI cadence on day one, before custom Power BI is built.

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Add a hard-dollar savings KPI

Add a flowlibs_BlendedHourlyRate environment variable (e.g. 75) and a Compose action that multiplies varHoursSaved by it. Bind the dollar figure into the email body and the Excel row so executives see hours and dollars side by side.

Switch the report cadence to weekly

Edit the Recurrence trigger to Weekly on Mondays at 06:00 and change the date window initialization to addDays(utcNow(), -7) -> utcNow(). The ROI snapshot table grows faster - add a retention policy that deletes rows older than 18 months.

Send a Teams adaptive card instead of an email

Replace the Outlook 365 SendEmailV2 step with a Microsoft Teams 'Post adaptive card in a chat or channel' action. Render the same KPIs in an adaptive card template and post into the leadership channel; keep the alert email branch as a safety net.

Drive Power BI instead of Excel

Drop the Excel AddRow / GetFileContent steps and let the ROI snapshot Dataverse table be the system of record. Build a Power BI report on flowlibs_roisnapshots and embed it; the flow keeps writing one row per month, Power BI handles the visuals.

Per-business-unit breakouts

Add a flowlibs_BusinessUnitColumn env var pointing at the column in flow-run-history that tags each run with a BU. Wrap the aggregation steps in an Apply to each over the distinct BU values and emit one Excel row + one email section per BU - useful in larger orgs where leadership wants per-team ROI.

Tighten the avg-manual-time model

Today every successful run earns its flow's avgMinutesPerRun. For long-running approval flows you may want to credit only the human-completed runs or apply a confidence factor per process. Add a flowlibs_avgmanualtime row column like 'confidenceFactor' (0.0 - 1.0) and multiply it in the hours-saved increment.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

startOfMonth(addToTime(utcNow(), -1, 'Month'))

startOfMonth(utcNow())

concat('createdon ge ', variables('varReportMonthStart'), ' and createdon lt ', variables('varReportMonthEnd'))

add(variables('varHoursSaved'), div(float(first(body('Filter_array_avg_manual_time'))?['avgMinutesPerRun']), 60))

div(mul(variables('varFailedRuns'), 100), max(variables('varTotalRuns'), 1))

if(greater(variables('varTotalRuns'), int(outputs('PriorSnapshot')?['prevRuns'])), 'up', if(less(variables('varTotalRuns'), int(outputs('PriorSnapshot')?['prevRuns'])), 'down', 'flat'))