Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
4 years ago
Solved

IF..... Max Value from another column

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

Data Protection
General Comment
Interesting Links
Need Help
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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 🙂

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.