How periodized hours are calculated

The periodized data holds one row per activity per point in time, and it holds two different kinds of value: planned hours spread across the activity's dates, and actual, earned and total planned hours recorded as a change at each import's cut-off. Knowing which is which explains rows that seem to contain nothing, earned hours that fall outside an activity's dates, and how to filter and sum the data correctly. This page is for report developers and data engineers reading periodized data through the REST API, GraphQL or the Parquet export. For what each column holds, see Parquet reporting columns.

Two kinds of value in one row

Planned, remaining and total-minus-actual hours

The schedule type's time-phasing scope decides which spreads are calculated: planned hours for Total (the default), remaining hours for Remaining, and total-minus-actual hours for Total minus actuals. A column whose scope is not selected stays empty. The three spreads differ in which hours are spread and over which part of the activity:

Planned hours ignore progress and the cut-off. The full planned work hours are spread over the activity's whole Start–Finish every time, so the planned curve shows the plan as it stands now, including periods already in the past.

Remaining hours show where the work that is left is expected to fall. Analytics sends the remaining scope (planned − earned) and the cut-off to the time-phasing service. The service takes the part of the activity's profile that lies after the cut-off, scales it so it adds up to the remaining scope, and spreads it from the cut-off to Finish. Some cases are handled separately:

• Not started: if progress is 0 and Start is after the cut-off, remaining equals planned, so the planned spread is copied for every period from the cut-off onwards.
• Complete: if progress is 100%, nothing is left, and the remaining hours are empty.
• Finish already passed: if the activity is not complete but its Finish is before the cut-off, there is no profile left after the cut-off. The whole remaining scope is placed in the first period at or after the cut-off.

Total-minus-actual hours work the same way, but they spread planned − actual hours instead of planned − earned. They show the hours left to spend rather than the work left to earn, so they differ from remaining hours whenever actuals and earned hours diverge. If actual hours equal planned hours, nothing is left to spend, and the column is empty.

Remaining and total-minus-actual hours are recalculated from the new cut-off on every Live import. The earlier spread is set to 0 and refilled, and rows before the new cut-off are emptied, so each import replaces the previous spread rather than adding to it. Summing the column gives what is left as of the latest cut-off. Unlike actual, earned and total planned hours, these values are not changes.

How an activity's hours are split

• Activities with resource assignments are spread per assignment. Each assignment uses its own planned hours, earned and actual hours, its own start and finish, its own calendar, and its own profile. Where the assignment has no start, finish or calendar of its own, the activity's is used. The results are added up per activity.
• Activities without resource assignments are spread from the activity's own planned hours, over its Start–Finish and its calendar. There is no profile, so the hours follow the calendar's working time evenly.
• Missing dates. If only Start or only Finish is set, the other is set to the same date and the hours land in a single period. If both are missing but the activity has planned hours, the hours are placed at the latest finish date in the schedule.

Columns by revision

Each row carries columns for all five revisions side by side: Live, original baseline, baseline, current and revised. The Live columns have no prefix. The other revisions use a prefix: OriginalBaseline…, Baseline…, Current… and Revised…. Not every kind of value exists for every revision, and some columns that do exist must not be used:

— means the revision has no such column.

Why the table looks like this

• Planned values exist for every revision. Planned hours, load and calendar hours are an estimate. Each revision spreads its own planned hours over its own Start–Finish, so Live can be compared with any baseline period by period.
• Total planned hours exist for every revision. Each revision records changes in its own scope, the same way as Live: the change since the previous import, in one row. The cut-off only moves forward with Live imports, so non-Live changes are also recorded at the Live cut-off that applies when the revision is imported or re-periodized. Summing them gives the revision's current total planned hours.
• Actual hours are Live only. Work is only ever reported against Live, so no other revision has an actual-hours column.
• Earned hours are Live only. Earned hours can only be calculated from Live progress against Live scope. The original baseline, current and revised revisions therefore have no earned column. BaselineEarnedHours still exists and still holds a number: Live progress applied to the baseline's total hours. It is obsolete; use EarnedHours.
• Remaining and total-minus-actual hours are meaningful for Live only. Both are spread from the cut-off onwards, and both depend on progress or actuals: remaining hours are planned minus earned, and total minus actual is planned minus actual. Progress, actuals and the cut-off only exist for Live. For the other revisions these columns are calculated from the progress and actuals stored in that revision's own file, against the Live cut-off, so they mix an old status into the current timeline. They are still written, but they are obsolete.

The Obsolete columns table lists every obsolete column with the column to use instead.

How the change per import is calculated

On every Live import, Analytics compares the activity's current values with the sum of all its earlier rows (rows dated before the cut-off), and records the difference:

A row is written only when at least one of the three changes is not zero. If an import brings no change in actuals, progress or scope, it adds no row.

Two consequences follow:

• Sums give the current values. Summing ActualHours, EarnedHours or TotalPlannedHours over all rows of an activity gives its actual hours, earned hours and planned work hours as of the latest import. Summing up to a date gives the values as they were reported at that date.
• Changes can be negative. If progress or actuals are corrected downwards, or scope is reduced, the change is recorded as a negative number.

The first import that contains an activity records its full planned work hours as TotalPlannedHours at that import's cut-off. That is why total planned hours often appear at an early date that looks unrelated to the activity's own dates. That date is the cut-off of the first import, not the date the activity was created.

Several imports with the same cut-off. A later import with the same cut-off replaces the values in that cut-off's row instead of adding a new one. If the replacement brings no change, the row's actual, earned and total planned hours are emptied.

Why earned and actual hours appear outside the activity's dates

Earned and actual hours are recorded when the change reaches Analytics, at the cut-off of the import that carried it, not on the dates the work was planned or done. If an activity is planned for 12–13 August but its progress and confirmations are only reported in an import with a cut-off of 2 September, the earned and actual hours appear on 1 September at 23:59:59.

To find when work was actually done, use the activity's actual start and finish dates on the report activity. Do not use the date of the earned-hours row.

Why rows with no hours exist

Rows where every hour column is 0 or empty are normal, and on a schedule that is imported often they can be the majority. They come from three places:

• Planned hours are recalculated on every import. Before the planned spread is written, every existing row of the activity has its planned values set to 0. The new spread then fills the periods the activity covers now. Rows are not deleted, so periods the activity covered before its dates moved stay in the data with 0 planned hours.
• Change rows only hold the change. A row at a cut-off holds only actual, earned and total planned hours. Its planned columns are 0 unless a planned period happens to fall on the same point in time.
• Remaining hours before the cut-off are emptied. If the schedule type uses remaining or total-minus-actual hours, those columns are emptied on every row dated before the cut-off, because remaining work only lies ahead of it.

0 and empty mean the same thing for any total. Neither marks an event.

Worked example

One activity, 15 planned hours, on a report schedule with daily points in time:

1. Import with cut-off 27 May. The activity first appears, planned for 9–10 June.
   • 26 May 23:59:59: TotalPlannedHours 15, ActualHours 0, EarnedHours 0
   • 9 June and 10 June: PlannedHours 7.5 each
2. Import with cut-off 5 August. The activity has moved to 12–13 August. Scope, progress and actuals are unchanged.
   • 9 June and 10 June: PlannedHours reset to 0. The rows stay.
   • 12 August and 13 August: PlannedHours 7.5 each
   • No change row, because nothing changed.
3. Import with cut-off 2 September. The activity is reported 100% complete with 1 hour of actuals.
   • 1 September 23:59:59: EarnedHours 15 (15 × 100% − 0), ActualHours 1, TotalPlannedHours 0

The activity now has six rows. Two of them (9 and 10 June) hold no hours. The sums give 15 planned, 15 earned, 1 actual and 15 total planned hours. The earned hours sit on 1 September, outside the activity's 12–13 August dates.

Filtering and summing correctly

• Removing rows where every hour column is 0 or empty is safe. It does not change any sum, cumulative curve or period total, and it can shrink a large table considerably.
• Filter on "all 0 or empty", not on "greater than 0". Earned, actual and total planned hours can be negative corrections. Dropping them makes the sums wrong.
• Sum, don't pick. For actual, earned and total planned hours, the value up to a date is the sum of all rows with PointInTime up to that date. The value in a single row is only the change at that cut-off.
• Use a date table for the time axis. Rows exist only where there is something to record, not for every period. In Power BI, relate PointInTime to a separate date table instead of relying on the rows to form a continuous axis.

Reading the data

The same rows are available through each supported interface. In all of them, a row joins to its report activity through the activity id.

Where the activity dates come from

The planned spread follows the activity's Start and Finish as they arrive from the planning system. For SAP work orders transferred through ILAP, Start and Finish are taken from the operation's constraint dates (START_CONS / FIN_CONSTR), not its scheduled early dates. If only one of the two is set, the other is set to the same date. If an operation has no constraint dates, its Start and Finish are empty.