How do you measure procurement stall days by opportunity stage in Dynamics 365 in 2027?

What it is and why it matters

Procurement stall days are the calendar days an opportunity sits inside a buyer-side approval stage — vendor onboarding, security review, legal redlines, purchasing sign-off — without forward movement. They are distinct from total sales cycle length, and they are distinct from rep inactivity. A deal can have daily email threads, a live Teams channel, and three touchpoints per week and still be stalled, because the constraint is not seller effort but the buyer's internal queue. Measuring stall days by stage is how a RevOps team separates "our reps are slow" from "their procurement department has a 34-day SLA and we planned around 10."

The reason this matters more in Dynamics 365 than in some other CRMs is architectural. Dynamics splits the concept of "where is this deal" across three different constructs, and teams routinely measure the wrong one. There is the Opportunity's own salesstagecode and stepname fields, which are legacy holdovers. There is the Business Process Flow (BPF), which is a separate entity — a record in a table like opportunitysalesprocess that holds activestagied, activestagestartedon, traversedpath, and completedon. And there is whatever custom option set your admin built because the out-of-the-box stages did not match how you sell. If procurement is a real gate in your funnel, it almost certainly lives in the third bucket, which means none of the built-in duration reporting sees it.

The financial consequence of not measuring this is concentrated in forecast accuracy. If your average enterprise deal spends 28 days in procurement and your reps commit deals with a close date 14 days out because "verbal yes, just paperwork now," you will miss the quarter on timing alone, not on win rate. Every deal you push is a deal that was forecast correctly on outcome and incorrectly on date. Stall-day measurement converts that guesswork into a distribution: if the median procurement stage duration is 21 days and the 90th percentile is 55, a deal that entered procurement 40 days ago is not "about to close," it is in the tail, and the forecast category should reflect that.

There is also a negotiation use for the data that most teams never exploit. Once you can show that deals entering procurement after the 15th of a month close 19 days later on average than deals entering before the 15th — a pattern that shows up in almost every organization with monthly approval committee cadences — your reps have a concrete reason to push a buyer to submit paperwork this week rather than next. That is an argument backed by your own instance's data, which is far more persuasive internally than an industry benchmark.

Finally, stall days are the cleanest input for renegotiating your own internal process. If your security questionnaire response time contributes eight of the median 21 stall days, that is not a buyer problem — that is a bottleneck you own. You cannot see that decomposition until you break the procurement phase into sub-stages and time each one separately.

The step-by-step process

Start with an audit, not a build. Open Advanced Find or the modern Power Apps maker portal and confirm three things: which BPF is actually attached to your opportunities, whether every opportunity has one attached, and whether the stage names in that BPF include a distinct procurement gate. In a large number of Dynamics instances, roughly a third of opportunities have a null BPF instance because they were created by an integration, imported in bulk, or cloned. Those records will silently drop out of every duration metric you build, so fix attachment before you fix measurement.

Second, decide where the timestamp lives. You have three viable options and they differ meaningfully in cost. Option one is the BPF entity's native activestagestartedon field, which Dynamics maintains for you but only for the *current* stage — the moment the deal advances, the prior stage's start time is gone unless you captured it. Option two is auditing: enable field-level auditing on the stage field and read history from the audit table. This is zero-config but the audit table is not queryable in Power BI without effort, retention is capped by your audit settings (commonly 30 to 90 days), and Microsoft has moved audit retention to a paid-storage model, so long-lookback reporting gets expensive. Option three, and the one most teams land on, is a custom stage-history table.

The custom table approach: create an entity named something like Opportunity Stage History with lookups to Opportunity, a text or option-set Stage Name, an Entered On datetime, an Exited On datetime, and a whole-number Days In Stage. Every stage transition writes a new row. This gives you one row per opportunity per stage entry, which handles the case that breaks every simpler design — deals that move backward. A deal that goes Proposal → Procurement → back to Proposal → Procurement again has two procurement intervals, and only a row-per-transition model can sum them correctly.

Third, build the writer. Use a Power Automate cloud flow with the Dataverse trigger "When a row is modified," scoped to the opportunity table, with the trigger column filter set to your stage field so it does not fire on every field edit. Add a "Get a row" step to find the open history row for that opportunity, patch its Exited On to utcNow(), compute Days In Stage with a div(sub(ticks(exited), ticks(entered)), 864000000000) expression, then create the new row for the incoming stage. If you prefer server-side, a synchronous plugin on the Update message with a filtering attribute on the stage field does the same work with lower latency and no flow-run consumption, at the cost of needing a developer and a solution deployment.

Fourth, handle the open interval. A deal currently sitting in procurement has an Entered On and a null Exited On, and its stall days grow every day. Do not compute this in a rollup field — Dynamics rollup fields recalculate on a 12-hour system job by default and cannot do "now minus field." Instead, either compute it at query time in Power BI with DATEDIFF(entered, IF(ISBLANK(exited), TODAY(), exited), DAY), or run a nightly scheduled flow that stamps a Current Days In Stage field on every open row. The Power BI approach is cheaper and always current; the stamped-field approach is necessary if you want the number visible on the form and usable in Dynamics views and alerts.

Fifth, backfill. New instrumentation gives you zero history on day one, and leadership will not wait a quarter for a baseline. Backfill from whatever you have: if auditing was on, parse the audit table for stage-change events on closed-won and closed-lost opportunities from the last two to four quarters and generate history rows. If auditing was off, approximate using activity timestamps — the first activity logged after a stage change is a rough proxy for entry, accurate to a few days. Label backfilled rows with a source flag so nobody mistakes an approximation for an instrumented measurement.

Sixth, define the metrics before you build the report, because the default choice is wrong. Do not lead with mean days in stage — procurement duration distributions are right-skewed, and one deal stuck in a 200-day legal fight will drag the mean past any threshold you set. Report median as the headline, 90th percentile as the risk signal, and count-in-stage-over-threshold as the operational number a manager can act on. Add a "stalled inventory" measure: total open pipeline dollars sitting in procurement beyond the 90th percentile. That is the one number an executive will actually read.

Costs, timelines, and typical ranges

Licensing first, because it determines which of the above paths you can take. Power Automate cloud flows using Dataverse connectors within the scope of your Dynamics 365 Sales license are generally covered — a stage-change flow that reads and writes Dataverse rows in the same environment is the canonical covered case. You cross into standalone Power Automate licensing when the flow calls premium non-Dataverse connectors or runs outside the app context. Microsoft revises these boundaries regularly, so verify against the current Power Platform licensing guide rather than trusting a prior implementation. Power BI Pro is per-user per-month and is the practical requirement for sharing a stall-days dashboard beyond yourself; Premium Per User or a capacity SKU only becomes relevant at large model sizes or with paginated report requirements you almost certainly do not have here.

Build effort, assuming a competent Dynamics admin and no custom development: the custom history table plus one write flow plus one nightly flow is roughly two to four days of hands-on work. The Power BI model — connecting via the Dataverse connector, writing the median and percentile DAX measures, building three or four visuals — is another one to three days for someone comfortable with DAX. Backfill is the wildcard and is usually the largest single line item: one to two weeks if you are parsing audit data with edge cases, near-zero if you accept starting fresh. A plugin-based writer instead of a flow adds developer time, a solution pipeline, and a test cycle, so budget one to two weeks rather than days.

Elapsed time from kickoff to a dashboard people trust is typically four to eight weeks, and the long pole is almost never technical. It is the definitional argument: what counts as procurement, whether security review is inside or outside it, whether a deal that goes quiet for three weeks and comes back is one interval or two. Get those decisions written down and signed off in week one, because relitigating them after the report is built means rebuilding measures.

On the numbers themselves — be careful with external benchmarks, and prefer your own baseline. Published procurement-cycle figures vary enormously by segment, contract value, and industry, and a mid-market SaaS deal and a public-sector hardware purchase share almost nothing. What is broadly reliable is the shape rather than the magnitude: procurement duration distributions are right-skewed with a long tail, deal size correlates positively with stall duration because higher spend crosses more approval thresholds, and regulated buyers plus public sector sit well above commercial medians. Build your thresholds from your own instance's 50th and 90th percentiles after two quarters of data, then revisit quarterly.

A useful way to set the alert threshold without a benchmark: compute the 75th percentile of procurement stage duration for closed-won deals only. Deals that eventually won and still took longer than that are your genuine outliers; anything past it is worth a manager touch. Setting the threshold from all deals including losses inflates it, because losses often sit in procurement forever before someone finally marks them closed.

Budget a recurring maintenance cost that teams forget. Every time someone edits the BPF — adds a stage, renames one, splits procurement into two — your history table's stage values fork and your DAX measures silently start excluding rows. Assign an owner, require a change ticket for BPF edits, and add a monthly check that the distinct stage names in the history table match the current BPF definition. That is maybe two hours a month, and skipping it is how a working dashboard quietly becomes a wrong one.

Where teams get it wrong

The most common failure is measuring the wrong entity. A team enables auditing on opportunity.stepname or builds a report on salesstagecode, gets clean-looking output, and never realizes those fields are not what the sales process actually drives — the BPF stage is. The symptom is a report where nearly every deal shows an identical duration, or where a large block shows zero days, because the field being tracked only changes on a subset of transitions. Verify by picking five real opportunities, checking their actual current BPF stage in the UI, and confirming your report shows the same stage for the same five records.

The second failure is calendar days versus business days without a stated choice. A deal entering procurement on a Thursday before a holiday weekend accrues four calendar days before anyone touches it. Both conventions are defensible — calendar days measure the buyer's experience, business days measure work capacity — but mixing them across reports guarantees a meeting where two people argue about a number and neither is wrong. Pick one, name the field accordingly (Calendar Days In Stage, not Days In Stage), and if you need business days, precompute them against a date dimension table with a holiday flag rather than trying to do it in a flow expression.

The third failure is ignoring backward movement. Every model that stores a single Entered On field directly on the opportunity record silently overwrites on re-entry, so a deal that bounces Procurement → Proposal → Procurement reports only the second interval. In a complex enterprise sale, backward movement is common, not exceptional, and undercounting it makes procurement look faster than it is — exactly the wrong direction for a metric whose purpose is to expose delay.

The fourth failure is timezone drift. Dataverse stores datetimes in UTC and Power Automate's utcNow() returns UTC, but a user-local datetime field configured with "User Local" behavior will display shifted, and a DAX DATEDIFF on day granularity across a UTC/local boundary can land a transition on the wrong calendar day. For duration fields, set the datetime behavior to Timezone Independent or Date Only at creation — behavior is difficult to change after the fact and in some cases requires recreating the field.

The fifth failure is instrumenting the measurement and never wiring it into a decision. A stall-days dashboard nobody opens changes nothing. It has to feed something concrete: a forecast-category rule where a deal past the 90th-percentile threshold cannot sit in Commit without a manager override and a written reason, an automated alert to the rep and manager at the threshold, and a standing agenda item in the weekly pipeline review that opens the stalled-inventory view first. If none of those exist, do not build the dashboard yet — build the rule first and instrument to support it.

A sixth, subtler failure: excluding open deals from the metric. If your report only includes closed opportunities, you have built a survivorship-biased view that systematically understates current stalling, because the deals stuck right now are the ones excluded. Report both — a closed-deal historical median for benchmarking, and an open-pipeline current-days view for action — and never let one substitute for the other.

Decision framework: when to choose what

The right implementation depends on three variables: how much history you need, whether you have developer access, and how many opportunities per month you process. Work through them in order.

If you need less than one quarter of lookback and have fewer than a few thousand stage transitions per month, native auditing plus a Power BI query against the audit table is genuinely sufficient and costs you a configuration toggle. Enable auditing on the BPF stage field, set retention to the longest your plan permits, and accept that you are renting rather than owning the data. This is the right answer for a team that wants to know whether procurement stalling is a real problem before investing in measuring it precisely. Treat it as a two-week diagnostic, not a permanent system.

If you need multi-quarter trending, you need the custom history table. There is no way around it — audit retention and audit query ergonomics both fail at that horizon. The remaining decision is flow versus plugin for the writer. Choose Power Automate when you have no developer, your volume is moderate, and a few seconds of write latency is acceptable. Choose a synchronous plugin when transitions are high-volume, when you need the history row committed inside the same transaction as the stage change so a rollback rolls both back, or when flow-run volume is pushing licensing thresholds. Plugins fail closed and loudly; flows fail open and quietly, which is a real reliability difference for a system of record.

If procurement in your business is genuinely multi-step — vendor onboarding, then security review, then legal, then purchasing — resist the urge to collapse it into one stage for reporting simplicity. Model it as sub-stages, either as additional BPF stages or as a separate option-set on the history row. The aggregate number tells you procurement takes 30 days; the decomposed number tells you 18 of those are your own security questionnaire turnaround, which is the only version that produces an action.

One more branch worth naming: whether to buy instead of build. Several revenue-intelligence and pipeline-analytics products connect to Dynamics and compute time-in-stage out of the box, and if your organization already owns one, checking whether it reads BPF stage history correctly is a five-minute question that can save the entire build. The caveat is that many such tools model a generic stage concept and map Dynamics BPF stages imperfectly, particularly with custom processes and backward transitions. Validate against five known deals before trusting the vendor's number — the same validation you would run on your own build.

Finally, sequence the rollout. Instrument one sales team or one segment for a full quarter before extending. Two things go wrong in a narrow pilot that are cheap to fix and expensive to fix at scale: stage definitions that turn out to mean different things to different reps, and BPF attachment gaps you did not know existed. Prove the numbers reconcile against five hand-checked deals, prove a manager changed a forecast call because of the data, then expand. A stall-days measurement that nobody trusts is worse than none, because it turns every pipeline review into a debate about the instrument rather than the deals.

Related questions

Can I get time-in-stage without any custom fields?

Partially. Enable field auditing on the BPF stage field and query the audit table — it captures every transition with a timestamp. Retention is limited by your audit settings and the data is awkward to model in Power BI, so treat it as a short-term diagnostic rather than a durable reporting foundation.

How do I handle deals that move backward through stages?

Store one history row per stage entry rather than a single Entered On field on the opportunity. Backward movement then produces multiple rows for the same stage, and your total is the sum of intervals. A single-field design silently overwrites and undercounts.

Should stall days be calendar days or business days?

Calendar days measure the buyer's actual wait and are simpler to compute; business days measure work capacity and better reflect team effort. Pick one, name the field explicitly, and if you need business days, join to a date dimension with holiday flags rather than computing in a flow.

Why do some opportunities show no stage duration at all?

Almost always a missing BPF instance — records created by integrations, bulk imports, or cloning often have no process attached. Audit for null BPF instances first; they drop out of every duration metric silently and can represent a substantial share of your pipeline.

What threshold should trigger a stalled-deal alert?

Use the 75th percentile of procurement stage duration among closed-won deals from your own instance. Deals that eventually won and still exceeded it are genuine outliers worth a manager touch. Including losses inflates the threshold because dead deals linger before anyone closes them.

FAQ

Does Dynamics 365 have a built-in stall days metric?

No. Dynamics 365 Sales has pipeline and stage views, and some Sales Insights and Sales Premium features surface stage-related analytics, but there is no out-of-the-box field that reports days spent in a specific stage across historical transitions. Every durable implementation involves either audit-table querying or a custom stage-history table with a flow or plugin writing rows on each transition.

Can I use a Dataverse rollup field to calculate days in stage?

Not for the open interval. Rollup fields recalculate on a scheduled system job — twelve hours by default — and cannot reference the current time, so a rollup cannot express "today minus stage entry date." Use a calculated field for closed intervals where both timestamps exist, compute the open interval at query time in Power BI, or stamp it nightly with a scheduled flow if you need it visible in Dynamics views.

How do I separate buyer-side procurement delay from our own internal delay?

Break the procurement phase into sub-stages and time each independently. Your security questionnaire turnaround, legal's redline response, and pricing approval are your delays; the buyer's committee calendar and budget cycle are theirs. Only decomposed timing distinguishes them, and the split usually surprises teams — a meaningful share of what gets blamed on buyer procurement is internal response latency.

Will this work if we use a custom Business Process Flow?

Yes, and custom BPFs are the common case. The history table stores whatever stage name the transition reports, so custom stages flow through unchanged. The maintenance requirement is that any BPF edit — adding, renaming, or splitting a stage — forks your stage values and can silently break DAX measures filtering on stage name. Add a monthly reconciliation check between distinct history-table stage values and the live BPF definition.

How long before the data is trustworthy enough to act on?

Plan on one full sales cycle plus a quarter. You need enough closed deals in the history table to compute a stable median and 90th percentile, and with enterprise cycles that is often two quarters. Backfilling from audit history shortens this considerably if auditing was already enabled, but flag backfilled rows so approximations are never mistaken for instrumented measurements.

What is the single most useful number to put in front of an executive?

Stalled inventory: total open pipeline dollars currently sitting in procurement stages beyond your 90th-percentile threshold. It converts a process metric into a revenue-at-risk figure, and unlike a median-days chart it maps directly onto a forecast conversation. Pair it with the count of affected deals so the number is actionable rather than just alarming.

Sources

• https://learn.microsoft.com/en-us/dynamics365/sales/create-business-process-flow
• https://learn.microsoft.com/en-us/power-apps/maker/data-platform/data-platform-processes
• https://learn.microsoft.com/en-us/power-apps/developer/data-platform/business-process-flows-entities
• https://learn.microsoft.com/en-us/power-platform/admin/manage-dataverse-auditing
• https://learn.microsoft.com/en-us/power-automate/dataverse/overview
• https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-rollup-fields
• https://learn.microsoft.com/en-us/power-apps/maker/data-platform/behavior-format-date-time-field
• https://learn.microsoft.com/en-us/dax/datediff-function-dax
• https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-dataverse
• https://learn.microsoft.com/en-us/power-platform/admin/pricing-billing-skus

Related on PULSE

• How do you audit data center leasing pipeline opportunity hygiene in Dynamics 365 during AE-led pods to prevent duplicate contacts after acquisition when multi-currency ARR rollups?
• Top 10 buying committee veto triggers that stall deals
• How are large buying committees in 2027 using consensus tools to stall deals?
• What's the sequence for getting executive sponsorship aligned before deal stall explodes into budget carry-forward?
• How can I phrase a question to help a rep distinguish between a real objection and a stall?