Which projects are on track, which are overdue, and where estimated hours diverge from reality. Generated by AI via Proxuma Power BI MCP server.
Which projects are on track, which are overdue, and where estimated hours diverge from reality. Generated by AI via Proxuma Power BI MCP server.
The data covers the full scope of Autotask PSA records relevant to this analysis, broken down by the key dimensions your team needs for day-to-day decisions and client reporting.
Who should use this: Project managers, operations leads, and MSP owners tracking delivery
How often: Weekly for status updates, milestone dates for escalation, monthly for portfolio review
Which projects are on track, which are overdue, and where estimated hours diverge from reality. Generated by AI via Proxuma Power BI MCP server.
EVALUATE
VAR today = TODAY()
RETURN ROW(
"ProjectsWithTasks", DISTINCTCOUNT('BI_Autotask_Tasks'[project_name]),
"TotalTasks", COUNTROWS('BI_Autotask_Tasks'),
"CompletedTasks", CALCULATE(COUNTROWS('BI_Autotask_Tasks'), 'BI_Autotask_Tasks'[status_name] = "Complete"),
"OverdueTasks", CALCULATE(COUNTROWS('BI_Autotask_Tasks'), 'BI_Autotask_Tasks'[status_name] <> "Complete", 'BI_Autotask_Tasks'[end_date] < today),
"ProjectsWithDates", CALCULATE(DISTINCTCOUNT('BI_Autotask_Tasks'[project_name]), NOT(ISBLANK('BI_Autotask_Tasks'[start_date])), NOT(ISBLANK('BI_Autotask_Tasks'[end_date])))
)Projects ranked by total task count, showing completion progress, estimated hours, and actual hours worked
| # | Project | Client | Status | Tasks | Completed | Window | Days | Worked |
|---|---|---|---|---|---|---|---|---|
| 1 | Project Happy | Mitchell-Everett | New | 132 | 0 | 2025-01-31 → 2025-03-14 | 42 | 0.0h |
| 2 | Project If | Mitchell-Everett | Complete | 132 | 132 | 2025-01-31 → 2025-03-14 | 42 | 0.0h |
| 3 | Project Police | Rivers, Rogers and Mitchell | Complete | 53 | 53 | 2024-10-14 → 2024-11-25 | 42 | 49.1h |
| 4 | Project Bag | Mitchell-Everett | New | 51 | 0 | 2025-03-01 → 2025-06-22 | 113 | 0.0h |
| 5 | Project Anyone | Ramos Group | In progress | 42 | 0 | 2025-09-01 → 2026-02-19 | 171 | 181.4h |
| 6 | Project Set | Smith and Sons | In progress | 41 | 1 | 2025-06-27 → 2026-02-06 | 224 | 101.5h |
| 7 | Project Environment | Lewis LLC | Complete | 36 | 36 | 2024-08-06 → 2025-08-06 | 365 | 1586.7h |
| 8 | Project Paper | Green PLC | Live | 30 | 26 | 2025-09-23 → 2026-01-13 | 112 | 158.2h |
| 9 | Project Eye | Patterson, Riley and Lawson | Complete | 22 | 22 | 2024-09-17 → 2025-02-14 | 150 | 47.5h |
| 10 | Project Set | Drake and Sons | Complete | 19 | 19 | 2024-04-30 → 2024-07-11 | 72 | 0.0h |
| 11 | Project Professional | Keith, Keller and Adams | Complete | 19 | 19 | 2024-08-05 → 2024-08-22 | 17 | 12.0h |
| 12 | Project Feel | Kelley-Walsh | Complete | 17 | 17 | 2024-01-25 → 2024-09-01 | 220 | 66.8h |
| 13 | Project Safe | George Ltd | Complete | 17 | 17 | 2025-02-10 → 2025-06-04 | 114 | 185.2h |
| 14 | Project Him | Doyle-Contreras | In progress | 16 | 5 | 2025-07-31 → 2025-12-16 | 138 | 88.6h |
| 15 | Project Along | Doyle-Contreras | Complete | 15 | 15 | 2025-03-10 → 2025-05-31 | 82 | 536.3h |
EVALUATE
TOPN(15,
ADDCOLUMNS(
SUMMARIZE(FILTER('BI_Autotask_Tasks',NOT(ISBLANK('BI_Autotask_Tasks'[start_date]))), 'BI_Autotask_Tasks'[project_name],'BI_Autotask_Tasks'[company_name],'BI_Autotask_Tasks'[project_status_name]),
"Tasks", CALCULATE(COUNTROWS('BI_Autotask_Tasks')),
"Completed", CALCULATE(COUNTROWS('BI_Autotask_Tasks'),'BI_Autotask_Tasks'[status_name] = "Complete"),
"EarliestStart", CALCULATE(MIN('BI_Autotask_Tasks'[start_date])),
"LatestEnd", CALCULATE(MAX('BI_Autotask_Tasks'[end_date])),
"TimelineDays", DATEDIFF(CALCULATE(MIN('BI_Autotask_Tasks'[start_date])), CALCULATE(MAX('BI_Autotask_Tasks'[end_date])), DAY),
"WorkedHours", CALCULATE(SUM('BI_Autotask_Tasks'[worked_hours]))
),
[Tasks], DESC
)Breakdown of all 1,409 tasks by current status, with average estimated and actual hours per status
| Status | Tasks | Share | Avg Est Hours | Avg Actual Hours | Hours Gap |
|---|---|---|---|---|---|
| Complete | 967 | 68.6% | 8.88h | 8.93h | +0.6% |
| New | 316 | 22.4% | 1.13h | 0.0h | Not started |
| In Progress | 119 | 8.4% | 8.87h | 13.24h | +49.3% |
| Planned | 4 | 0.3% | 7.0h | 1.75h | -75% |
EVALUATE
SUMMARIZE(
BI_Autotask_Tasks,
BI_Autotask_Tasks[status_name],
"Tasks", COUNT(BI_Autotask_Tasks[task_id]),
"AvgEstimated", AVERAGE(BI_Autotask_Tasks[estimated_hours]),
"AvgActual", AVERAGE(BI_Autotask_Tasks[hours_worked])
)
ORDER BY [Tasks] DESCProjects where actual hours significantly exceed estimates, signaling scope creep or under-estimation
The portfolio sits at 68.6% task completion across 202 projects. That headline number looks acceptable, but it hides two problems. First, 434 tasks (30.8%) are overdue. Second, the 119 in-progress tasks are running at an average of 13.24 actual hours against 8.87 estimated, a 49% overshoot that compounds with every week those tasks stay open.
Project Happy and Project Dawn together account for 183 tasks with zero completion. Both show 0 estimated hours and 0 hours worked. These are either not yet started, abandoned, or parked without anyone formally closing them out. Either way, they inflate your active project count and distort portfolio-level completion metrics.
Project Gamma is finished (36 of 36 tasks complete) but ran 382 hours over budget: 1,587 actual hours against 1,205 estimated. That is a 31.7% overrun. If this project was billed on a fixed-fee basis, that gap came straight out of margin. If it was time-and-materials, the client saw a bill significantly larger than the original scoping indicated.
On the other end, Project Edge delivered all 49 tasks in 423 hours against a 1,027-hour estimate. That is 58.8% under budget. While that sounds positive, such a large gap suggests the estimate was padded or the scope was cut significantly during delivery. Either way, the estimate was not useful for capacity planning.
The in-progress bucket deserves the most attention right now. Those 119 tasks averaging 49% over their time estimates are your current risk. Every additional day they stay open, the variance grows. Focus standups on these tasks this week.
5 priorities based on the findings above
Tasks that are in progress and already averaging 49% over their estimated hours are your biggest active risk. Pull a list of every in-progress task, sort by hours overshoot, and have each project lead confirm whether the remaining work is scoped correctly. Rescope or escalate anything over 2x the original estimate.
Together these hold 183 tasks with zero completion and zero hours logged. If they are on hold, mark them as such in Autotask so they stop appearing in active project counts. If they are abandoned, close them. Keeping dead projects in your active pipeline distorts every metric in this report.
Project November finished with a 387.5% hours overshoot (8 estimated, 39 actual). Project Gamma overran by 382 hours in absolute terms. Run a retrospective on these projects to understand whether the estimates were rushed, whether scope changed mid-project, or whether the estimation method itself needs calibration. Bad estimates today become bad margins tomorrow.
Project Alpha (53 tasks, 49 hours worked, 0 estimated), Project Flow (42 tasks, 181 hours worked, 0 estimated), and several others have no time estimates at all. Without estimates, you cannot track variance, plan capacity, or spot overruns. Make estimation mandatory for new project tasks going forward.
Project Feel delivered 88% of tasks with actual hours 34.5% under estimate. Project Mike hit 91.7% completion at 31.1% under budget. Both show healthy task throughput with accurate-to-conservative estimates. Use their task-level data as reference points when scoping similar engagements.
All data comes from the BI_Autotask_Tasks table in Proxuma Power BI. This table syncs project tasks from Autotask PSA, including status, estimated hours, actual hours worked, and project assignment. The AI writes DAX queries against this table to calculate aggregates like completion rates and hours variance.
Completion rate is the number of tasks with status "Complete" divided by the total task count for that project. A project with 31 completed tasks out of 71 total has a 43.7% completion rate. This is a task-count metric, not an hours-weighted metric.
A task is counted as overdue when its resolved_due_age_days value is greater than zero in the Autotask data. This means the task has passed its due date without being marked complete. The 434 overdue tasks in this report represent 30.8% of all tasks across the portfolio.
Projects with 0 estimated hours either were not scoped with time estimates in Autotask, or the estimates were entered at the project level rather than the task level. Without task-level estimates, variance analysis is not possible. We recommend making estimated hours a required field on project tasks.
Yes. Connect Proxuma Power BI to your Autotask PSA, add an AI tool (Claude, ChatGPT, or Copilot) via MCP, and ask the same question. The AI writes the DAX queries, runs them against your real project data, and produces a report like this in under fifteen minutes.
Connect Proxuma Power BI to your PSA, RMM, and M365 environment, use an MCP-compatible AI to ask questions, and generate custom reports - in minutes, not days.
See more reports Get started