Conditional Format for the future Periods

Hi Experts,


I have a query related to dashboard, I have a line item called "Potential Retirement Date" in date format.


1. Whenever the date/year is 2019(current year) + 2 years(till 2021) it should show Orange color.

2. Whenever the date/year is 2018(Previous Year) is should show Red color.

3. Whenever the date/year is more than 2 years of current year i.e. from 2022 it should show no color.


I have done with the above 1 & 2 points, but in 1st point it is working fine to 2019 + 1 year and not +2 years, cuz in the model calendar the number of future years is 1. so it is working fine.

It is working fine for the previous years as no of past years is 2.

So Could someone help me with the solution.






  • @CommunityMember82499 

    What I would do is create a time system module and check for the first and last periods


    1st Period? = OFFSET(1, -1, 0) = 0
    Last Period? = OFFSET(1, 1, 0) = 0
    Timescale Start = IF '1st Period?' THEN START() ELSE BLANK
    Timescale End = IF Last Period? THEN END() ELSE BLANK 


    Then you can bring these dates into a lookup module:


    Start of Timescale = TIMESUM(Time Settings.Timescale Start)
    End of Timescale = TIMESUM(Time Settings.Timescale End)

    Future Date Check = ADDYEARS(End of Time Scale, 'Threshold (Yrs)')

    The threshold is for you to adjust as and when necessary. 


    Best practice tip: It is better to parameters stored in a constants/lookup module to avoid hard coding


    You can then reference the values in the above module to derive the numeric values needed for conditional formatting

    I hope this helps


  • Hi David,


    Thanks for the detailed description but I could able to articulate my line item "Condition" to the newly created modules, could you please help me with the formula for the same.


    That Condition should works for the "Potential Retirement Date" line item.




  • It would be something like


    IF Potential Retirement date > Future Date Check then x else ....


    So to get no colour you'll need to use a 3 colour conditional format with "blank" in the middle and use lower, mid and high to reference the colours

    The formula above will be the middle number of the formatting

    I usually use -1 , 0, 1 for the parameters to keep it simple


    So IF low condition THEN -1 else IF high condition THEN 1 ELSE 0


    Hope that helps