How to do a compare based on two columns

[tewtom]

Thanks for helping!  We have two columns of data- column 1 is a date, column 2 is a text field with a pending action .  This along with a primary key of project number.  We want to be able to choose dates, and have a visual list those dates that have the same pending actions.  For example-

Project	Source File Date	Pending_Activity
TD1005855	1/1/2023	08-All Requirements Met Not Completed
TD1005855	10/29/2023	08-All Requirements Met Not Completed
TD1005855	11/5/2023	08-All Requirements Met Not Completed
TD1005855	11/12/2023	08-All Requirements Met Not Completed
TD1005855	11/26/2023	08-All Requirements Met Not Completed
TD1013540	1/1/2023	01-Cancelled/Pending Cancellation
TD1013540	10/29/2023	02-Closed
TD1013540	11/5/2023	02-Closed
TD1013540	11/12/2023	02-Closed
TD1013540	11/26/2023	02-Closed
TD1018901	1/1/2023	13-PreConstruction Not Completed
TD1018901	10/29/2023	11-ATP for Construction Not Completed
TD1018901	11/5/2023	10-WO Sent to RPPM Not Completed
TD1018901	11/12/2023	15-Electrical Construction Not Started
TD1018901	11/26/2023	17-Electrical Construction Not Completed

We want to be able to create a list of projects where the pending activity for the latest week- 11/26/2023- matches the pending activity on 1/1/2023.  In the sample above, only TD1005855 would show in the visual.  We have about 30,000 projects, and want a visual that would illustrate.  We MAY also want to compare week to week, but for now lookingonly to compare current week to 1/1/2023.  Any thoughts are appreciated.

[gmsamborn · Accepted solution]

Hi @tewtom 

If you don’t have a Project dimension table:
In Power Query, create a new query by referencing your table. Remove all columns except [Project]. Remove duplicates. Close Power Query.

Create a 1:* relationship between ‘Project’[Project] and ‘Table’[Project] (single)

3 measures:

_FirstStatus = 
    IF(
        ISINSCOPE( 'Project'[Project] ),
        CALCULATE(
            MAX( 'StatusTable'[Pending_Activity] ),
            'StatusTable'[Source File Date] = DATE( 2023, 1, 1 )
        )
    )

_LatestStatus = 
 VAR _MaxDt =
    MAXX(
        ALL( 'StatusTable' ),
        [Source File Date]
    )
RETURN
    IF(
        ISINSCOPE( 'Project'[Project] ),
        CALCULATE(
            MAX( 'StatusTable'[Pending_Activity] ),
            'StatusTable'[Source File Date] = _MaxDt
        )
    )

Compare = 
    COUNTROWS(
        FILTER(
            VALUES( 'Project'[Project] ),
            [_FirstStatus] = [_LatestStatus]
        )
    )

Let me know if you have questions.

Compare latest status to first status.pbix

[tewtom]

Looks simple and GREAT, will try tonight and give thumbs up tomorrow, THANKS for the help!