“Are we on schedule and on budget?” is the question every project review starts with. In Power BI you can answer it with two simple bars per project, and, as a bonus, catch bad dates in Acumatica that nobody has updated.
% of timeline elapsed
% of Timeline Elapsed =
VAR _asOf = EOMONTH ( MAX ( FiscalPeriod[EndDate] ), 0 )
VAR _start = MAX ( Project[StartDate] )
VAR _finish = MAX ( Project[ProjectedSubstantialCompletionDate] )
RETURN
DIVIDE ( _asOf - _start, _finish - _start )Two decisions from a real client project worth copying:
- Use the projected substantial completion date (the PM’s current estimate), not the contract end date or the required completion date.
- Measure as of the end of the selected month, not TODAY(), so past months are reproducible.
Put it next to % complete
Show % of Timeline Elapsed and % Complete (cost-based) as two bars per project. Roughly equal is healthy. Time far ahead of cost means the job is behind, or the dates are stale.
A data-quality flag
When we first put this on screen, several projects showed more than 100% of time elapsed while still having work left. The completion dates simply hadn’t been updated in Acumatica. The client asked for something that would “poke them in the eye”, so we added a flag:
Data Quality Flag =
IF (
[% of Timeline Elapsed] > 1
|| [% Earned Revenue] > [% of Timeline Elapsed],
1,
0
)- Time elapsed above 100% on an open project means the projected completion date is in the past.
- Earned revenue far ahead of the timeline isn’t impossible, but it’s worth a review of both dates and budget.
Show a ⚠ icon column in the project table and a card with the number of flagged projects. The flag improves the source data, not just the report: PMs fix the dates in Acumatica because they don’t want to be on the list.
Want this in your reports?
I build Power BI for Acumatica project dashboards. See the pricing guide or get in touch.
