topic Re: Help needed for 'Overdue Items' in Power BI in DAX Commands and Tips

Help needed for 'Overdue Items' in Power BI

Anonymous — Thu, 17 Jun 2021 12:47:06 GMT

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!

Re: Help needed for 'Overdue Items' in Power BI

dedelman_clng — Thu, 17 Jun 2021 13:57:33 GMT

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

Re: Help needed for 'Overdue Items' in Power BI

Anonymous — Thu, 17 Jun 2021 17:12:30 GMT

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!

Re: Help needed for 'Overdue Items' in Power BI

dedelman_clng — Thu, 17 Jun 2021 17:20:15 GMT

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

Re: Help needed for 'Overdue Items' in Power BI

Anonymous — Fri, 18 Jun 2021 05:45:29 GMT

Thank you so much! This worked perfectly