Calculating on/off schedule

[PC2022]

Below is my DAX to calculate schedule status - on schedule, off schedule, on watch (if the due date is within 45 day from today). DAX works but not completely. The third task on the screenshot below should be red (off schedule because dates are in the past and percent complete is 0). The last task should be yellow because actual finish date is within 45 days from today. I tried changing order of commands but no luck.

SchKPI = 

VAR DaysUntil = DATEDIFF(today(), msdyn_projecttask[Finish], DAY)

VAR ScheduleStatus = SWITCH(TRUE(),

msdyn_projecttask[Baseline Finish] = BLANK(), BLANK(),

msdyn_projecttask[% Complete] = 100 && msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",

msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",

msdyn_projecttask[Finish] > TODAY(), "On Schedule",

(msdyn_projecttask[% Complete]<100 && DaysUntil < 0) || msdyn_projecttask[Finish] > msdyn_projecttask[Baseline Finish], "Off Schedule",

msdyn_projecttask[% Complete]<100 && DaysUntil < 45, "On Watch",

"Error") RETURN ScheduleStatus

[DataNinja777]

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,

[PC2022]

I used your sequence but unfortunately it didn't work. See screen shot below, it marked all task red. I tried moving "on watch" on top, it marked all tasks yellow.

[v-tianyich-msft]

Hi @PC2022 ,

Try the expression and if it still doesn't work, please share the sample file:

SchKPI = 
VAR DaysUntil = DATEDIFF(TODAY(), msdyn_projecttask[Finish], DAY)
VAR ScheduleStatus = SWITCH(
    TRUE(),
    msdyn_projecttask[Baseline Finish] = BLANK(), BLANK(),
    msdyn_projecttask[% Complete] = 100 && msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",
    msdyn_projecttask[% Complete] < 100 && DaysUntil < 0, "Off Schedule",
    msdyn_projecttask[% Complete] < 100 && DaysUntil < 45, "On Watch",
    msdyn_projecttask[Finish] <= msdyn_projecttask[Baseline Finish], "On Schedule",
    msdyn_projecttask[Finish] > TODAY(), "On Schedule",
    "Error"
)
RETURN ScheduleStatus

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PC2022 · Accepted solution]

I replaced "100" with "1" and it worked.

[PC2022]

Thank you but it didn't work. How do i share a file? It only allows for a link not actual file.