Filter columns and compare dates

[JamesCoco]

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 have a single control item on the list that provides a date for each document, indicating when it was last updated.

What I want to do is create a table, for each document(column), that compares each employee's entered date against the control date. So I can then produce some charts showing the percentage of employees that have read each document.

Am new to PowerBI and am struggling to find the answer to what I'm looking to do, which is probably very simple. I'd really appreciate some tips!

[JamesCoco]

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]

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]

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]

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]

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?

[Anonymous]

Hi @JamesCoco ,

How did you execute unpivot step in Power Query Editor? Please review the content in the following link and check whether they can help you resolve the problem.

sharepoint loading error (A value without a type name was found and no expected type is available)

If the above one is not working, please provide your sample pbix file(exclude sensitive data) in order to make troubleshooting and find the solution. Thank you.

Best Regards

[PhilipTreacy]

Hi @JamesCoco 

How exactly do you want to compare dates? What is it you are trying to gauge?

Presumably you want to know if someone has read the document since it's last update date?

If so you can check if the person's reading date is equal to or after the document update date.

Please share some data so I can understand your data structure.

Regards

Phil