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 ,

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?