IF..... Max Value from another column

[cristianml]

Hi,

Need help with the following formula that is not working.

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"))))

Thanks

[v-angzheng-msft · Accepted solution]

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.

[TheoC]

Hi @cristianml 

Although there are a few other ways to achieve what you're after, I think you should remove CALCULATE and re-test your output. Also, adjust your -1 and -2 to be outside of your MAX ( Table[Column] ) (i.e. MAX ( Table[Column] ) -1...)

If that doesn't work, I'll put you a quick calculated column together.

Theo 🙂