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
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

     

     

     

    • JamesCoco's avatar
      JamesCoco
      Regular Visitor
      5 years ago

       

      Hi Rena, this seems to be what I'm looking for, however, I'm running into errors when unpivoting from my SharePoint list. As soon as I unpivot the columns, I get errors in every column, as displayed.

       

      I tried creating a brand new list, but had the same problems. Perhaps a problem when sourcing from a SharePoint list?