topic Re: Calculating on/off schedule in DAX Commands and Tips

Calculating on/off schedule

PC2022 — Tue, 26 Nov 2024 14:48:25 GMT

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

Re: Calculating on/off schedule

DataNinja777 — Tue, 26 Nov 2024 15:03:20 GMT

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,

Re: Calculating on/off schedule

PC2022 — Tue, 26 Nov 2024 16:37:33 GMT

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.

Re: Calculating on/off schedule

Anonymous — Wed, 27 Nov 2024 05:26:47 GMT

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.

Re: Calculating on/off schedule

PC2022 — Wed, 27 Nov 2024 15:09:12 GMT

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

Re: Calculating on/off schedule

PC2022 — Tue, 03 Dec 2024 18:26:14 GMT

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