Filter columns and compare dates

Question

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

philiptreacy · Answer

Hi&nbsp;JamesCoco&nbsp;
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

jamescoco · Answer

Hi Phil, thanks for replying.&nbsp;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.&nbsp;The setup is as simple as:&nbsp;Sorry, this forum is refusing to accept the table as "valid HTML" so I'll just have to write it out plain text.&nbsp;Title&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Document1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Document2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Document3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IDJoe Employee&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4Alan StaffMember&nbsp; &nbsp; &nbsp; 28/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5ControlItem&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1

anonymous · Answer

Hi&nbsp;JamesCoco&nbsp;,
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. &nbsp;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?&nbsp; 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&nbsp;Alan StaffMember&nbsp;, Document1:&nbsp;Read (28/01/2021&lt; 01/02/2021)&nbsp;Document2:&nbsp;Unread(01/02/2021&gt;01/01/2021)&nbsp;Document3:&nbsp;Read(13/01/2021&lt;01/02/2021), then count the number of read and undread?

Title&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Document1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Document2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Document3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ID
Joe Employee&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4
Alan StaffMember&nbsp; &nbsp; &nbsp; 28/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5
ControlItem&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1

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

jamescoco · Answer

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

Hi&nbsp;JamesCoco&nbsp;,
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] )
                &amp;&amp; 'Table'[Title] = "ControlItem"
        )
    )
RETURN
    IF (
        'Table'[Title] = "ControlItem",
        BLANK (),
        IF ( 'Table'[Date] &gt;= _controldate, "Read", "Unread" )
    )​
3. Create a column chart(Axis: Documents&nbsp; Legend: New calculated column[Status] Values: count of Title) and filter Status is not blank
Best Regards
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Filter columns and compare dates

7 Replies

Related content

Slicer for filtering several columns

filter comparing two column

Compare Dynamic Columns in a Matrix

Compare two columns in different tables

Comparing columns error