Intermediate and Advanced DAX for Program Analytics

💡 Key concepts

• Fiscal YTD: TOTALYTD and DATESYTD take a year-end date argument:

  Actual Obligations FYTD =
      TOTALYTD ( SUM ( fact_Budget[ActualObligation] ), dim_Date[Date], "9/30" )
  

• Cumulative to date (contract-to-date EVM):

  BCWP Cum =
  VAR MaxDate = MAX ( dim_Date[Date] )
  RETURN
      CALCULATE ( [BCWP], dim_Date[Date] <= MaxDate, REMOVEFILTERS ( dim_Date ) )
  
  ACWP Cum = VAR MaxDate = MAX ( dim_Date[Date] ) RETURN CALCULATE ( [ACWP], dim_Date[Date] <= MaxDate, REMOVEFILTERS ( dim_Date ) )
  BCWS Cum = VAR MaxDate = MAX ( dim_Date[Date] ) RETURN CALCULATE ( [BCWS], dim_Date[Date] <= MaxDate, REMOVEFILTERS ( dim_Date ) )
  
  CPI Cum = DIVIDE ( [BCWP Cum], [ACWP Cum] )
  SPI Cum = DIVIDE ( [BCWP Cum], [BCWS Cum] )
  
  BAC =  // BAC repeats each period; take it from the latest period only
  VAR LastPeriod = CALCULATE ( MAX ( fact_EVM[PeriodEnd] ), dim_Date[Date] <= MAX ( dim_Date[Date] ), REMOVEFILTERS ( dim_Date ) )
  RETURN CALCULATE ( SUM ( fact_EVM[BAC] ), fact_EVM[PeriodEnd] = LastPeriod, REMOVEFILTERS ( dim_Date ) )
  
  EAC (CPI method) = DIVIDE ( [BAC], [CPI Cum] )
  VAC              = [BAC] - [EAC (CPI method)]
  TCPI (BAC)       = DIVIDE ( [BAC] - [BCWP Cum], [BAC] - [ACWP Cum] )
  

  Note: The BAC pattern assumes every control account reports in the latest period. Discuss what happens if one doesn't, and how you'd fix it (e.g., latest period per control account using SUMX over dim_WBS).

• Inactive relationships:

  Milestones Forecast in Period =
      CALCULATE ( COUNTROWS ( fact_Milestones ), USERELATIONSHIP ( fact_Milestones[ForecastDate], dim_Date[Date] ) )
  

• % of total: DIVIDE([Actual Obligation], CALCULATE([Actual Obligation], ALLSELECTED(dim_Program)))
• Selected-value titles: "Program: " & SELECTEDVALUE(dim_Program[ProgramName], "All Programs")
• RAG measure for conditional formatting:

  CPI RAG Color =
  SWITCH ( TRUE (),
      ISBLANK ( [CPI Cum] ), BLANK (),
      [CPI Cum] >= 0.95, "#2E7D32",
      [CPI Cum] >= 0.90, "#F9A825",
      "#C62828" )
  

• Debugging: DAX query view and Performance Analyzer. Build measures one step at a time with VARs and return each intermediate VAR to check it.

🛠 Hands-on exercise: EVM and Execution Measures

Data: fact_EVM (PeriodEnd, BCWS, BCWP, ACWP, BAC, EAC_Contractor), fact_Budget, fact_Milestones, dim_Date.

1. Implement all cumulative EVM measures above. Validate PRG-005's latest CPI Cum and EAC against your Excel EVM_SNAPSHOT LAMBDA. They should match.
2. Add EAC Gap = [EAC (CPI method)] - SUM(fact_EVM[EAC_Contractor]) for the latest period (a common analyst question: is the contractor's EAC optimistic?).
3. Build fiscal YTD obligations vs. plan, and Obligation Rate FYTD.
4. Build Milestones Forecast in Period and Milestones Completed in Period with USERELATIONSHIP.
5. Apply CPI RAG Color as field-value conditional formatting on a matrix.

📦 Mini-project: Time-Phased Execution Matrix

A matrix with Program on rows and FY month on columns, showing Planned vs. Actual obligations FYTD, with a variance measure and conditional formatting. Add a measure Months Behind Plan that estimates how many months of plan the actual FYTD obligation equals (a stretch task: use a VAR table of cumulative monthly plan and compare).