Which projects have overdue tasks, hour overruns, and scheduling conflicts that signal resource allocation problems. Generated by AI via Proxuma Power BI MCP server.
Which projects have overdue tasks, hour overruns, and scheduling conflicts that signal resource allocation problems. 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 have overdue tasks, hour overruns, and scheduling conflicts that signal resource allocation problems. Generated by AI via Proxuma Power BI MCP server.
EVALUATE ROW(
"MilestoneItems", CALCULATE(COUNTROWS('BI_Autotask_Billing_Items'), 'BI_Autotask_Billing_Items'[sub_type] = 21),
"MilestoneAmount", CALCULATE(SUM('BI_Autotask_Billing_Items'[total_amount]), 'BI_Autotask_Billing_Items'[sub_type] = 21),
"MilestoneContracts", CALCULATE(DISTINCTCOUNT('BI_Autotask_Billing_Items'[contract_id]), 'BI_Autotask_Billing_Items'[sub_type] = 21),
"MilestonesWithNoProject", CALCULATE(COUNTROWS('BI_Autotask_Billing_Items'), 'BI_Autotask_Billing_Items'[sub_type] = 21, ISBLANK('BI_Autotask_Billing_Items'[project_id]))
)The top 9 projects ranked by overdue task count. Projects with high overdue counts and hour variances are the strongest indicators of resource allocation conflicts.
| Project | Tasks | Completed | Overdue | Est. Hours | Actual Hours | Variance | Status |
|---|---|---|---|---|---|---|---|
| Project Happy | 132 | 0 | 132 | 0 | 0 | No est. | Critical |
| Project Core | 71 | 31 | 40 | 303 | 317 | +4.6% | Over budget |
| Project Dawn | 51 | 0 | 51 | 0 | 0 | No est. | Critical |
| Project Flow | 42 | 0 | 42 | 0 | 181 | Unbounded | Critical |
| Project Beta | 52 | 31 | 21 | 93 | 13 | -86.0% | Overdue |
| Project Feel | 92 | 81 | 11 | 855 | 560 | -34.5% | On track |
| Project Him | 16 | 5 | 11 | 122 | 89 | -27.0% | Overdue |
| Project Gamma | 36 | 36 | 0 | 1,205 | 1,587 | +31.7% | Over budget |
| Project Kilo | 30 | 26 | 4 | 90 | 158 | +75.6% | Over budget |
EVALUATE
TOPN(15,
ADDCOLUMNS(
SUMMARIZE(
BI_Autotask_Tasks,
BI_Autotask_Tasks[project_name],
"Tasks", COUNT(BI_Autotask_Tasks[task_id]),
"Completed", CALCULATE(COUNT(BI_Autotask_Tasks[task_id]),
BI_Autotask_Tasks[status_name] = "Complete"),
"EstHours", SUM(BI_Autotask_Tasks[estimated_hours]),
"WorkedHours", SUM(BI_Autotask_Tasks[hours_worked])
),
"OverdueTasks", [Tasks] - [Completed],
"Variance", DIVIDE([WorkedHours] - [EstHours], [EstHours])
),
[OverdueTasks], DESC
)
ORDER BY [OverdueTasks] DESCSide-by-side comparison for projects that have both estimated and actual hour data. Overruns indicate resource allocation exceeded planning capacity.
Portfolio-wide distribution of task statuses across all 202 projects
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] DESCThree distinct resource allocation conflict patterns visible in the data
Project Happy (132 tasks), Project Dawn (51 tasks), and Project Flow (42 tasks) have zero completed tasks. Happy and Dawn also have zero estimated hours. These projects were created in Autotask but never properly scoped or staffed. They sit in the system consuming project slots and creating false signals in portfolio health reports. Project Flow is worse: it has 181 hours logged against zero estimates, meaning resources are working on it without any plan.
Project Gamma is 100% complete but consumed 1,587 hours against a 1,205-hour estimate, a 31.7% overrun. Project Kilo is 86.7% complete with a 75.6% overrun (158 hours vs 90 estimated). Both projects finished or are finishing the work, but at a cost that was never planned for. This is the classic sign of resources being pulled between competing projects, forcing overtime and rework.
Project Beta logged only 13 hours against a 93-hour estimate (-86.0%) while still having 21 overdue tasks. This points to a project that was planned but never received the resources it was promised. Project Feel is running 34.5% under estimate with 88% completion, which looks efficient on paper. But the 11 remaining overdue tasks suggest the final deliverables are stalled, possibly because the team has moved on to other work.
The portfolio has 1,409 tasks across 202 projects, with a 68.6% completion rate. That number looks reasonable until you look at the distribution. The overdue count of 434 tasks (30.8%) is concentrated in a handful of projects, and three of those projects have made zero progress at all.
Project Happy is the single largest problem. With 132 overdue tasks and zero completions, it represents nearly a third of all overdue tasks in the portfolio. It has no hour estimates, which means it was never properly scoped. Either this project needs to be cancelled and removed from Autotask, or it needs an immediate planning session to assign resources and set realistic timelines.
The hour variance data tells a different story from the overdue counts. Project Gamma is complete on every task, but it burned through 382 extra hours beyond its estimate. That is the equivalent of roughly 10 weeks of a single engineer's time. Project Kilo has the highest percentage overrun at 75.6%, and it still has 4 tasks outstanding. If the pattern holds, the final cost will exceed the estimate by nearly double.
Milestone billing revenue of $3.85M is tied to these projects. When projects run over budget and behind schedule, the margin on that revenue shrinks. The gap between estimated and actual hours across this sample is around 15% on projects that have both data points. Applied to the milestone revenue, that represents significant margin erosion.
The most actionable finding is the three ghost projects. 225 tasks across Happy, Dawn, and Flow are clogging the portfolio with no plan, no progress, and in Flow's case, unbounded hours. Cleaning these up would immediately drop the overdue rate from 30.8% to under 15%.
5 priorities based on the findings above
Project Happy (132 tasks), Project Dawn (51 tasks), and Project Flow (42 tasks) need an immediate decision: scope and staff them, or archive them. They account for 225 of 434 overdue tasks (51.8%). Leaving them open distorts every project health metric you have. Project Flow is the most urgent because resources are logging hours against it with no estimate or plan.
At 75.6% over estimate with 4 tasks still open, this project is heading toward double its original budget. Pull the open task list, estimate the remaining work, and set a hard cap. If the remaining tasks require more than 20 hours, consider de-scoping. The client should be notified about timeline adjustments before the overrun grows further.
The project is done, so there is nothing to fix operationally. But 31.7% over budget on a 1,205-hour project is a pattern that needs to be understood. Was the estimate wrong? Were resources pulled away and brought back? Was scope added mid-project without adjusting the estimate? The answers will help you estimate future projects more accurately.
With only 13 hours logged against a 93-hour estimate and 21 overdue tasks, this project looks like it was approved but never resourced. Check whether the assigned team members were pulled into other projects. If so, that is a direct allocation conflict. Either re-assign dedicated resources or push the timeline out and inform the client.
Three of the nine highlighted projects have zero hour estimates. Without estimates, there is no way to detect overruns, measure efficiency, or predict resource needs. Make it a standard practice: no project goes active in Autotask without estimated hours on every task. This single change makes future reports immediately more useful.
The BI_Autotask_Contract_Milestones table is empty in the demo data model. In production, Proxuma Power BI populates milestone data from your Autotask contracts. For this demo report, project-level task data from BI_Autotask_Tasks serves as a proxy, grouping tasks by project to approximate milestone-level analysis. The DAX patterns are the same; only the source table changes.
A resource allocation conflict occurs when the same people or teams are assigned to multiple projects with overlapping timelines, causing delays in one or more. In this report, we detect conflicts through their symptoms: high overdue task counts, hour variances exceeding 25%, and projects with logged hours but no completion progress. These are the measurable effects of allocation problems.
Hour variance is calculated as (Actual Hours - Estimated Hours) / Estimated Hours. A positive variance means the project used more hours than planned. A negative variance means it used fewer. Projects with zero estimated hours show as "No est." because division by zero is undefined. These projects need estimates added before variance tracking is meaningful.
This is the total contract revenue for contracts with billing sub-type 21 (milestone billing) in Autotask. It represents the revenue tied to milestone-based delivery. When projects with milestone billing run over budget, the margin on this revenue decreases because the cost of delivery exceeds what was planned when the contract was priced.
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 milestone and task data, and produces a report like this in under fifteen minutes. With real data, the BI_Autotask_Contract_Milestones table will be populated, giving you true milestone-level analysis.
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