Cheat sheet
EVM formulas (Appendix A) plus quick syntax pulled from the modules.
EVM formulas
| Metric |
Formula |
Interpretation |
| BCWS (PV) |
Planned value of work scheduled |
The plan |
| BCWP (EV) |
Planned value of work actually performed |
What you earned |
| ACWP (AC) |
Actual cost of work performed |
What you spent |
| CV |
BCWP − ACWP |
Negative = over cost |
| SV |
BCWP − BCWS |
Negative = behind schedule (in $ terms) |
| CV % |
CV / BCWP |
|
| SV % |
SV / BCWS |
|
| CPI |
BCWP / ACWP |
< 1.0 = cost inefficiency |
| SPI |
BCWP / BCWS |
< 1.0 = behind schedule |
| BAC |
Total budget at completion |
|
| EAC (CPI method) |
BAC / CPI |
One independent estimate. Others combine CPI and SPI. |
| VAC |
BAC − EAC |
Negative = projected overrun |
| TCPI (BAC) |
(BAC − BCWP) / (BAC − ACWP) |
Efficiency needed on remaining work to finish on budget |
| TCPI (EAC) |
(BAC − BCWP) / (EAC − ACWP) |
Efficiency needed to hit the EAC |
Use cumulative values for CPI/SPI/EAC unless you specifically want current-period efficiency. RAG thresholds in this curriculum (0.95 / 0.90) are illustrative. Use your organization's thresholds.
Federal fiscal year snippets
| Tool |
Fiscal year from a date |
| Excel |
=YEAR(A2)+(MONTH(A2)>=10) |
| Power Query M |
Date.Year([Date]) + (if Date.Month([Date]) >= 10 then 1 else 0) |
| DAX (calc column) |
YEAR('dim_Date'[Date]) + IF(MONTH('dim_Date'[Date]) >= 10, 1, 0) |
| DAX time intelligence |
TOTALYTD([Measure], dim_Date[Date], "9/30") |
| pandas |
df["FY"] = df.Date.dt.year + (df.Date.dt.month >= 10) |
| Power Automate |
if(greaterOrEquals(int(formatDateTime(utcNow(),'MM')),10), add(int(formatDateTime(utcNow(),'yyyy')),1), int(formatDateTime(utcNow(),'yyyy'))) |
Quick syntax by tool
Excel
| Task |
Formula |
| Lookup with fallback |
=XLOOKUP(id, T[ID], T[Val], "Not found") |
| Latest record |
=XLOOKUP(id, T[ID], T[Val], , 0, -1) |
| RAG band lookup |
=XLOOKUP(cpi, Bands[Min], Bands[RAG], , -1) |
| Filter with OR |
=FILTER(T, (T[Status]="Late")+(T[Status]="At Risk")) |
| Running total |
=SCAN(0, range, LAMBDA(a,x,a+x)) |
| Safe CPI |
=LAMBDA(ev,ac, IF(ac=0, NA(), ev/ac)) |
| FY month headers |
=EOMONTH(DATE(2025,10,1), SEQUENCE(1,12,0)) |
Power Query M
| Task |
Code |
| Rename variant headers |
Table.RenameColumns(t, {{"Fcst Dt","ForecastDate"}}, MissingField.Ignore) |
| Safe conversion |
try Number.From(x) otherwise null |
| Each shorthand |
each [Cost] * 2 = (_) => _[Cost] * 2 |
| Unpivot months |
Table.UnpivotOtherColumns(t, {"ProgramID","Milestone"}, "FYMonthName", "Amount") |
DAX
| Task |
Measure |
| Safe ratio |
CPI = DIVIDE([BCWP], [ACWP]) |
| Cumulative |
CALCULATE([BCWP], dim_Date[Date] <= MAX(dim_Date[Date]), REMOVEFILTERS(dim_Date)) |
| Fiscal YTD |
TOTALYTD([Actual Obligation], dim_Date[Date], "9/30") |
| Inactive relationship |
CALCULATE(COUNTROWS(fact_Milestones), USERELATIONSHIP(fact_Milestones[ForecastDate], dim_Date[Date])) |
| Dynamic title |
"Program: " & SELECTEDVALUE(dim_Program[ProgramName], "All Programs") |
pandas
| Task |
Code |
| Coerce dates |
pd.to_datetime(s, errors="coerce") |
| Safe merge |
pd.merge(a, b, on="ProgramID", how="left", validate="many_to_one", indicator=True) |
| Cumulative EVM |
df.sort_values("PeriodEnd").groupby("ProgramID")[["BCWS","BCWP","ACWP"]].cumsum() |
| Latest per group |
df.sort_values("PeriodEnd").groupby("ProgramID").tail(1) |
| Unpivot |
df.melt(id_vars=[...], var_name="Month", value_name="Amount") |
Power Automate
| Task |
Expression |
| Excel serial → date |
addDays('1899-12-30', int(item()?['ForecastDate']), 'yyyy-MM-dd') |
| Local date |
convertFromUtc(utcNow(), 'Eastern Standard Time', 'yyyy-MM-dd') |
| Slip days |
div(sub(ticks(Forecast), ticks(Baseline)), 864000000000) |
| Any rows? |
greater(length(body('Filter_array')), 0) |