IF..... Max Value from another column

Hi, cristianml 

Try this:

Fiscal Year =
IF(
    'Rev Seq Database'[Current or Prior] = "Current",
    CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) ),
    IF(
        'Rev Seq Database'[Current or Prior] = "Prior",
        CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) - 1 ),
        IF(
            'Rev Seq Database'[Current or Prior] = "Prior -1",
            CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) - 2 ),
            "qtd"
        )
    )
)

Or:

Fiscal Year =
SWITCH(
    TRUE(),
    'Rev Seq Database'[Current or Prior] = "Current", CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) ),
    'Rev Seq Database'[Current or Prior] = "Prior", CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) - 1 ),
    'Rev Seq Database'[Current or Prior] = "Prior -1", CALCULATE( MAX( 'Revenue - CCI'[Fiscal Year] ) - 2 ),
    "qtd"
)

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.