Help needed for 'Overdue Items' in Power BI

[Anonymous]

I am working on creating a report that will show any items that are due past a certain due date. I am hoping to create either a measure or a new column with this info.

The columns I need to use to get this data is:

Action Due Date : This has the date that the item is due by.
Closed Date: The date that the item was closed on.
Status: This shows if the item is 'complete' 'ongoing' or 'not started'

I need the logic to be = If item is not 'complete' then check the Action Due Date by the Closed Date to see if it is overdue, and then mark it as maybe 1? and if its not due yet, then mark as 0?

I am then hoping to use this to create a count to see how many overdue items are assigned to each person.

Not sure how to go about this, would appreciate any help!

[dedelman_clng]

Hi @Anonymous -

Try this column

IsOverdue = 
var __Status = [Status]
var __ActionDt = [Action Due Date]
var __ClosedDt = [Closed Date]
return
IF ( __Status = "Complete", 0,
      IF ( __ActionDt > __ClosedDt, 1, 0 )
)

Wasn't quite clear on the logic of Action Due Date vs Closed Date, but this is the basic code structure you would want.

Hope this helps

David

[Anonymous]

Hi David, thank you for this! This halfway works, just have a quick question about it.

So any item that has 'complete' as the action status works perfectly and it counted as '0'. But any items that are not due yet are also being counted as overdue.

For example: An item is due 07/01/2021, and its closed date (which is the date that the item is finished) is blank since there is still time to 'complete' it, but the logic is treating it as overdue and calculating '1'.

Is there a way to include TODAY in the logic. As in, as of TODAY, the item is not overdue if it is compared to the action due date? 

Really appreciate the help!

[dedelman_clng · Accepted solution]

Hi @Anonymous -

Yes, we can add TODAY() in there. Try this out

IsOverdue = 
var __Status = [Status]
var __ActionDt = [Action Due Date]
var __ClosedDt = [Closed Date]
return
IF ( __Status = "Complete", 0,  //Filter out "Complete"
    IF ( __ActionDt >= TODAY(), 0,  //Filter out future due dates
      IF ( __ActionDt > __ClosedDt ||  //Closed item from the past was overdue when completed
               ISBLANK( __ClosedDt ),  //Open item has due date in the past
          1, 0 )
    )
)

Hope this helps

David

[Anonymous]

Thank you so much! This worked perfectly