Retrieve a max value from given dynamic periods


Hi, Anaplaners


I posted a similar question before (, but this time I have faced an issue that cannot be solved by the solution I get to received in the previous question. Thing is that (see the attached) the line item "MAX Value" see its value and values of next two months to fetch a max value. (For example, FY19 Jan sees FY Jan, Feb, and Mar to get a max value of the three months, and FY19 Feb sees FY19 Feb, Mar, and Apr) Can you give me pointers to solve this ?

Thanks in advance



Best Answer

  • kavinkumar

    HI Gregory,


    You can bring it in this way. Create 2 line items and apply offset formula to get current period +1 and +2 and then in your Max value line item refer Max(source line,value +1,value +2) ,you will get the answer.Retrieve a max value from given dynamic periods.PNG

    Retrive Max.PNG








  • Thanks, Kavin.

    A simple solution but perfectly worked for me !!

  • Hi Gregory, As alternative to OFFSET function you can try to use function Next (), so the final formula could be like this: Max Value = MAX(Value, NEXT(Value), NEXT(NEXT(Value)) )