CHANGE
CHANGE(level over time, [from], [shape])
Turns a running total, recorded at each update, into what was added between updates: an amount over time. Progress, hours booked and cost to date are reported as totals; CHANGE gives what each period contributed, which is what a chart adds up week by week. It takes a level, so its input is almost always HISTORY of a field.
Arguments
| Argument | What to give it |
|---|---|
| level over time | A level, such as HISTORY([% complete]) or HISTORY([Actual units]). An amount or a date over time is refused. |
| from | Optional date where the first update's value starts to build up, such as the task's [Actual start]. Left out, the first update's whole value lands at once, just before that update. |
| shape | Optional. Where in each stretch between updates the change lands, as SPREAD's shape: an amount over time (in proportion to its part in each piece of the stretch; an amount that is all at one moment puts the change there) or a level over time (in proportion to working time times the level). Usually the task's working window, SPREAD(1, [Actual start], COALESCE([Actual finish], DATADATE())). A CURVE is refused. |
Each later change builds up over the working time between one update and the next. An update with no value is skipped: the change is spread across the gap. With a shape, a change lands only where the shape has weight in its stretch; where it has none there (the task didn't work between those two updates, or the shape is blank on the row), the change builds up evenly over the stretch, as with no shape. A change never leaves its own stretch, so what an update reported to date stays where it was.
Examples
Formula=CHANGE(HISTORY([% complete]), [Actual start])
The progress made in each period between updates, starting from the day the task actually started.
Formula=CHANGE(HISTORY([% complete]), [Actual start], SPREAD(1, [Actual start], COALESCE([Actual finish], DATADATE())))
The same, but only on the days the task actually worked: progress reported at an update after the task finished lands between its actual start and finish, not up to the update.
Formula=CHANGE(HISTORY([% complete])) / 100 * SUM(Assignments[Budgeted units] * Assignments[Hourly rate])
Earned value, period by period: each period's progress times the task's budget.
Formula on assignments=CHANGE(HISTORY([Actual units]))
On an assignment: the hours booked between each update and the last, ready to add up into actual hours per week.
Reach for something else when
| You want | Use |
|---|---|
| The total to date at an update, not what was added | AT of the HISTORY |
| Planned work over time, from dates | SPREAD |
| How much was added in all, as one number | TOTAL of the CHANGE |
Common questions
Why is CHANGE of a spread refused?
A spread is already an amount: it says what is added when. CHANGE is for totals to date. The editor says: "CHANGE: it needs a level over time, such as HISTORY([…]), not Amount over time".
The editor refuses this=CHANGE(SPREAD(INHOURS([Duration]), [Start date], [Finish date]))
Why is a CURVE refused as the shape?
A curve is a shape over one span, and only SPREAD has one. CHANGE has a stretch per update: give it the curve laid out as an amount, SPREAD(1, [Start date], [Finish date], CURVE([Start date], [Finish date], "front")). The editor says: "A CURVE is a shape for SPREAD and goes only there: SPREAD(amount, from, to, CURVE(from, to, "back"))".
=CHANGE(HISTORY([% complete]), [Actual start], CURVE([Start date], [Finish date], "front"))
Why does the first period get everything when I leave out from?
With no earlier update to measure from, the first value has no stretch to build up over, so it lands just before the first update. Give the task's [Actual start] (or its baseline start) to spread it from there.