Milestone OTIF metric

Options
edited 12/11/23

I've been given a new KPI of on time and in full (two different mesurables) for project milestones and I'm not sure how to best track the "on time" part of the milestones. How would you figure the % of milestones that are hit on time? It feels like it would need to be very manual.

• ✭✭✭✭✭✭
Options

In that case, you would use a helper column (can be hidden after setting up) with a formula that indicates whether it was on time or late.

=IF([Actual Date]@row <> "", IF([Actual Date]@row<= [Target Date]@row, "On Time", "Late"))

Then to get the percentage that are "On Time" you would use

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "")

• ✭✭✭✭✭✭
Options

Do you have a date column for the target completion and another date column for the actual completion?

• Options
• ✭✭✭✭✭✭
Options

In that case, you would use a helper column (can be hidden after setting up) with a formula that indicates whether it was on time or late.

=IF([Actual Date]@row <> "", IF([Actual Date]@row<= [Target Date]@row, "On Time", "Late"))

Then to get the percentage that are "On Time" you would use

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "")

• Options

Would you use the following to find the % On Time for Milestones (MR) only

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "") AND COUNTIFS([MR]:[MR], @cell = "TRUE"

• ✭✭✭✭✭✭
Options

You would include that range/criteria set in both of the already existing COUNTIFS. You would not use a third COUNTIFS.

• Options

You are AWESOME thank you

• ✭✭✭✭✭✭
Options

Happy to help. 👍️