Forum Discussion

JamesCoco's avatar
JamesCoco
Regular Visitor
5 years ago

Filter columns and compare dates

I have a SharePoint list with about 30 date columns, each representing a document in our library. Each employee adds their name to this list and the date that they have read each document.   I then...
Need Help
JamesCoco's avatar
JamesCoco
Regular Visitor
5 years ago

Hi Phil, thanks for replying.

 

You're correct in your assumption yes, i am just looking to see who has read / not read since the last update and also the total numbers of read/unread.


The ID is just the row ID on the Sharepoint list, so I'm assuming I'll be able to use that to reference the control item as part of the formula.

 

The setup is as simple as:

 

Sorry, this forum is refusing to accept the table as "valid HTML" so I'll just have to write it out plain text.

 

Title                            Document1             Document2             Document3             ID

Joe Employee             01/02/2021             02/01/2021              20/01/2021           4

Alan StaffMember      28/01/2021               01/02/2021            13/01/2021            5

ControlItem                01/02/2021              01/01/2021             01/02/2021            1

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JamesCoco ,

    There are two tables in your scenario: one is for employee to fill their data and the date reading the document, the another one is for you to control every document date.  What's the calculation logic? Do you want to compare the date in the table which filled by the employee with the date of each document in your control item table?  If it is less than that, it means that a certain document has been read at the specified time, otherwise, no?Base on your sample data, for example: employee Alan StaffMember , Document1: Read (28/01/2021< 01/02/2021) Document2: Unread(01/02/2021>01/01/2021) Document3: Read(13/01/2021<01/02/2021), then count the number of read and undread?

    Title                            Document1             Document2             Document3             ID

    Joe Employee             01/02/2021             02/01/2021              20/01/2021           4

    Alan StaffMember      28/01/2021               01/02/2021            13/01/2021            5

    ControlItem                01/02/2021              01/01/2021             01/02/2021            1

    Whether my understanding is correct? If no, please correct me and provide more details. Thank you.

    Best Regards

    • JamesCoco's avatar
      JamesCoco
      Regular Visitor
      5 years ago

      Hi Rena, thanks for the reply. There is actually only one list at the moment, the control item is just an item on the list, alongside the employees. So I'm looking to compare every employee's entered dates against the dates on the one control item (on the same list).

       

      If the employee date for a document (column) is greater or equal to the date on the same column on the control item, then it means they have read it. If the employee date is less, they have not read it.

       

      So ultimately yes, I'd like to be able to produce a visual that shows a list of all that have read/unread as well as an overall count, for each document(column) in the original list.

       

      Hopefully that all makes sense now.

       

      Thanks again,

       

      James

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi JamesCoco ,

        I created a sample pbix file (see attachment) for you, please check whether that is what you want.

        1. Unpivot the document fields in Power Query Editor

        2. Create a calculated column as below

        Status = 
VAR _controldate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Document] = EARLIER ( 'Table'[Document] )
                && 'Table'[Title] = "ControlItem"
        )
    )
RETURN
    IF (
        'Table'[Title] = "ControlItem",
        BLANK (),
        IF ( 'Table'[Date] >= _controldate, "Read", "Unread" )
    )​

        3. Create a column chart(Axis: Documents  Legend: New calculated column[Status] Values: count of Title) and filter Status is not blank

        Best Regards