Skip to main content

To my knowledge, the API doesn’t have a specific field for whether or not a work order is overdue, so we need to create a new value in order to expand on the existing reports.

This is the base formula I’m using:

 

OverdueState = 

// Work order is considered overdue if eCompleted On] > &Due Date]
IF(
'WorkOrders'dCompleted on]
> 'WorkOrders'dDue Date]
, TRUE
, FALSE
)

I’ve tried filtering by various combinations of work order creation, close, & updated timestamps and Status=CANCELED/SKIPPED. The numbers are always close to what shows up on MaintainX reporting, but are always slightly off. 

Has anyone had any luck recreating the overdue logic that MaintainX reporting uses?

Hey Mike, have you checked if some of these work orders don’t have a due time along with the due date?

If they have it, and they’re completed past due time, the reporting module will flag them as “Overdue”. However, per your formula, they would be overlooked.


Hi ​@sergiodiniz ,

I just confirmed none of the work orders in my sample have due times. Thank you for the tip though, I’ll have to watch out for this in the future!


Circling back on this in case anyone runs into this problem.

Because there is no due time assigned to the due dates, Power BI reads the due date timestamp as 12:00AM. Work orders closed on the due date would have a timestamp later than 12:00AM, so these would be considered overdue.

Fixed by adding a “+1” to the due date. 

 

Resulting formula:

OverdueState = 
// Work order is considered overdue if rCompleted On] > nDue Date]
// Also consider overdue if TODAY() > Due Date

IF(
'WorkOrders'kCompleted on].tDate]
> 'WorkOrders'kDue Date].eDate]+1

, TRUE
, FALSE
)

Now it just needs to also consider open work orders whose due date has passed!


Reply