Calculating on/off schedule

Hi PC2022 ,

The issue lies in the evaluation order of conditions within your SWITCH(TRUE(), ...) statement. The conditions are being evaluated sequentially, and some conditions (e.g., "On Schedule") are matching prematurely, causing unexpected results.

To fix this, adjust the order of conditions to prioritize the most specific ones first. Here's the corrected DAX formula:

SchKPI = 
VAR DaysUntil = DATEDIFF(TODAY(), msdyn_projecttask[Finish], DAY)
VAR ScheduleStatus = SWITCH(
    TRUE(),
    // Off Schedule: % Complete < 100, past due, or Finish > Baseline Finish
    (msdyn_projecttask[% Complete] < 100 && DaysUntil < 0) || msdyn_projecttask[Finish] > msdyn_projecttask[Baseline Finish], "Off Schedule",
    
    // On Watch: % Complete < 100 and Finish is within 45 days from today
    msdyn_projecttask[% Complete] < 100 && DaysUntil < 45, "On Watch",
    
    // On Schedule: % Complete = 100 and Finish <= Baseline Finish
    msdyn_projecttask[% Complete] = 100 && msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",
    
    // On Schedule: Finish <= Baseline Finish
    msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",
    
    // On Schedule: Finish is in the future
    msdyn_projecttask[Finish] > TODAY(), "On Schedule",
    
    // Default case for debugging
    "Error"
)
RETURN ScheduleStatus

• The evaluation order in SWITCH(TRUE(), ...) is crucial because it stops as soon as a condition matches.
• By moving the "Off Schedule" and "On Watch" checks to the top, these conditions are applied first.
• This resolves the specific issues you described:
• The third task is correctly marked "Off Schedule" because it meets the overdue condition.
• The last task is correctly marked "On Watch" because its finish date is within 45 days from today.

Let me know if this resolves your issue or if you need further clarification!

Best regards,