CLIP
CLIP(value over time, from, to)
Cuts out the part of a value over time between two dates: the hours that fall this month, or the cost still ahead of the data date. The result is still over time, not a number, so read it with TOTAL when you want how much falls in the window.
Arguments
| Argument | What to give it |
|---|---|
| value over time | An amount (from SPREAD, CHANGE, or a SUM of them) or a level (from HISTORY). One row's value: add a list up with SUM first. |
| from, to | Dates. The cut is exact, to the second. An empty or reversed window holds nothing. Given a period set's dates, p[Start date] and p[Finish date], it cuts once per period and gives a list on the periods. |
Work that lands all at once at an instant belongs to the window that starts there: CLIP(x, d, …) holds it, CLIP(x, …, d) doesn't.
Examples
Formula=TOTAL(CLIP(SPREAD(INHOURS([Duration]), [Start date], [Finish date]), [Start date], DATADATE()))
The hours the task was planned to have worked by the data date.
Formula=CLIP(SPREAD(INHOURS([Duration]), [Start date], [Finish date]), DATADATE(), [Finish date])
The hours still ahead of the data date, kept over time for a forecast chart.
Measure (on the project)=TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), DATADATE(), ADDWORK(DATADATE(), 10d)))
As a measure: the hours planned across the project over the next ten working days, a two-week look-ahead.
Measure (on the project)=LET(m, PERIODS("month", [Start date], [Finish date]), MAX(TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), m[Start date], m[Finish date]))))
The busiest month's hours: the project's hours cut into each month, each month's total, and the largest.
Reach for something else when
| You want | Use |
|---|---|
| The running total up to a date, not the part in a window | AT |
| The whole amount | TOTAL on its own |
| A date as forecast at some point | AT of HISTORY of the date field |
Common questions
Why can't I clip a date's history?
A forecast finish over time is a date at each moment, with nothing to add up inside a window. The editor says: "CLIP cuts out an amount or a level; a date over time has no part in a window. Read it on a date with AT(…, date)".
The editor refuses this=CLIP(HISTORY([Finish date]), [Start date], [Finish date])
Why is a list of spreads refused?
CLIP reads one row's value. The editor says: "CLIP reads one row's value over time, not a list of them: add them up first, e.g. CLIP(SUM(Tasks[…]), …)".
Why must the two lists of dates come from one set?
Each period is cut by its own start and finish, so they must line up: "CLIP's lists come from different sets, so they don't line up. Read both from one set, e.g. p[Start date] and p[Finish date]".