Monthly snapshot of overdue items

[sdavidson]

I have a sharepoint list of reports each with a due date that is 2 weeks after the report. 

I want to measre the number of open overdue reports on a weekly or monthly basis. 

The problem is that you can only look back on the number of open overdue reports retrospectively.

When a report gets closed, it is no longer an open report and therefore is removed from the visual.

I want to know what items were open and overdue at that time, not what status those items are now. 

It currently only shows what is currently left open from that month, not what was open at the end of that month. 

I hope this makes sense. 

Im sure there is something simple i am missing but have gone in a bunch of circles. any help is much appreciated. 

[darkniqht · Accepted solution]

Here’s a simple approach:

Set up a new SharePoint list to take a snapshot of open overdue reports at the end of each week or month. You could use a workflow or a Power Automate flow to automate this.

At the end of each period, have the flow run and copy the current open overdue reports into your snapshot list. Include columns for the report name, due date, and the date of the snapshot.

This snapshot list will allow you to see what was overdue at the end of each period, regardless of their current status.

This way, you maintain a historical record of open overdue reports, making it easier to analyze trends over time.

[sdavidson]

Thank you! Thats so much simpler than i was thinking. Its easy to get it stuck in your head that "this can all be done in power bi" but not see that just doing some in power automate would make things a lot easier. 

[johnt75]

Do you have the date at which the report was closed ? If so you could create a measure like 

Item Overdue =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR OpenDate =
    SELECTEDVALUE ( 'Table'[Open Date] )
VAR CloseDate =
    SELECTEDVALUE ( 'Table'[Close Date] )
VAR Result =
    IF (
        OpenDate <= ReferenceDate
            && (
                ISBLANK ( CloseDate )
                    || CloseDate > ReferenceDate
            ),
        1
    )
RETURN
    Result

and then use that as a filter to only show when the value is 1.