Home Forums Power Pivot Finding overdue documents every month

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 1 week ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #18168

    curls90
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hello All,

    I’m new to powerpivot and have come across a problem I’m not able to solve at the moment, I’ve still got so much to learn…..

    I need to show a total count of all documents that are overdue for review. The business rule is a document becomes overdue if it was registered and it took more then 7 days to move the lifecycle to completed. The system timestamps the document when it is registered, it also timestamps it when a person reviews the document and indicates this by progressing the lifecycle to completed.

    I have columns named “Registered” and “Completed” that are of date types. The completed dates can be blank indicating the document has not yet progressed to completed. There also many days in which multiple documents are registered. The data looks like this;
    <table>
    <tbody>
    <tr>
    <td>Registered</td>
    <td>Completed</td>
    </tr>
    <tr>
    <td>1/08/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>1/08/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>2/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>16/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>13/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>12/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>4/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>5/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>2/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>2/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>1/08/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>31/07/2019</td>
    <td>31/07/2019</td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td>4/08/2019</td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td>4/08/2019</td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td>2/08/2019</td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td>8/08/2019</td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td></td>
    </tr>
    <tr>
    <td>30/07/2019</td>
    <td></td>
    </tr>
    </tbody>
    </table>
     

    <span style=”display: inline !important; float: none; background-color: transparent; color: #333333; cursor: text; font-family: Georgia,’Times New Roman’,’Bitstream Charter’,Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;”>The goal is to produce a line chart showing the total overdue items for every month (last day of the month) in order to develop some trend analysis. The total has to be all overdue documents in the table, not just overdue for this month.</span>

    Any help on this would be greatly appreciated.

    #18169

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2580
    • Total: 2587

    What version of Excel are you using (2010, 2013, 2016 or later)?

    #18170

    curls90
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi, thnks for replying.

    I’m using 2016.

    I also have a date table setup with relationships defined.

    #18171

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2580
    • Total: 2587

    Please see attached workbook.

    Depending on whether or not you count Registered as part of the 7 days or not, your formula could look like:

    
    
    Count of Completed More Than 7 Days after Registration :=
    COUNTROWS (
    FILTER (
    Table1,
    VAR vIfCompletedIsBlankThenTodayIsTheEarliestPossibleCompletionDate =
    IF ( ISBLANK ( Table1[Completed] ), TODAY (), Table1[Completed] )
    RETURN
    IF (
    1.0 * ( vIfCompletedIsBlankThenTodayIsTheEarliestPossibleCompletionDate - Table1[Registered] ) > 7,
    TRUE(),
    FALSE()
    )
    )
    )
    
    
    Attachments:
    You must be logged in to view attached files.
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.