The problem
Percent spent alone cannot compare contracts of different value and duration.
Measures
Planned Value =SUMX ( Contracts, Contracts[Value] * DIVIDE ( MIN ( MAX ( 'Date'[Date] ), Contracts[End] ) - Contracts[Start], Contracts[End] - Contracts[Start] ))Earned Value = SUM ( Claims[WorkDoneValue] )Actual Cost = SUM ( Claims[PaidValue] )SPI = DIVIDE ( [Earned Value], [Planned Value] )CPI = DIVIDE ( [Earned Value], [Actual Cost] )Elapsed % = DIVIDE ( [Planned Value], SUM ( Contracts[Value] ) )Spent % = DIVIDE ( [Actual Cost], SUM ( Contracts[Value] ) )Status =VAR Gap = [Elapsed %] - [Spent %]RETURN SWITCH ( TRUE (), ISBLANK ( Gap ), BLANK (), Gap < 0.10, "Excellent", Gap < 0.15, "Good", Gap < 0.20, "Weak", "At risk" )Classification rule
Gap = % time elapsed − % spent
Gap
+18
- Excellent
- Good
- Weak
- At risk
Illustrative example
| Contract | Time elapsed | Spent | SPI | CPI | Status |
|---|---|---|---|---|---|
| Contract A | 62% | 58% | 0.96 | 1.02 | Excellent |
| Contract B | 71% | 54% | 0.82 | 0.97 | Weak |
| Contract C | 48% | 25% | 0.61 | 0.92 | At risk |
Selecting the latest claim
// "Latest row wins" is only safe if the latest row is complete.Latest Claim =VAR LastComplete = CALCULATE ( MAX ( Claims[ClaimNo] ), NOT ISBLANK ( Claims[Contractor] ), NOT ISBLANK ( Claims[Scope] ), ALLEXCEPT ( Claims, Claims[ContractNo] ) )RETURN CALCULATE ( SUM ( Claims[WorkDoneValue] ), Claims[ClaimNo] = LastComplete )Result
- One verifiable standard for every contract.
- The same status in the board, report and deck.
Figures and examples are illustrative.