Show data based on the latest/maximum year

Hi AnthonyJoseph ,

This code should match better with your requirements:

Revenue = 
VAR _LastYear = 
    CALCULATE (
        MAX ( 'Table'[Year] ),
        ALLSELECTED ()
    )
VAR _ProjectsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Project] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ClientsLastYear = 
    CALCULATETABLE (
        VALUES ( 'Table'[Client] ),
        'Table'[Year] = _LastYear,
        ALLSELECTED ()
    )
VAR _ProjectsInContext = VALUES ( 'Table'[Project] )
VAR _ClientsInContext = VALUES ( 'Table'[Client] )
VAR _ClientProjectCombinationsInLastYear =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[Client],
            'Table'[Project]
        ),
        'Table'[Year] = _LastYear,
        ALLEXCEPT ( 'Table', 'Table'[Client], 'Table'[Project] )
    )
VAR _RevenuePerClientProject =
    CALCULATETABLE (
        ADDCOLUMNS (
            _ClientProjectCombinationsInLastYear,
            "@Revenue",
            CALCULATE ( SUM ( 'Table'[revenue] ) )
        ),
        INTERSECT ( _ClientsLastYear, _ClientsInContext ),
        INTERSECT ( _ProjectsLastYear, _ProjectsInContext )
    )
RETURN
SUMX ( _RevenuePerClientProject, [@Revenue] )

revenue projects last year examples

The 40 for Client 2, Project 2, Year 2020 in your examples seems rather like a mistake in your example?Example not matching
BR
Martin