How to do a compare based on two columns

Hi Anonymous 

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