Occasional Contributor

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.




Community Boss

Re: Conditional Format for the future Periods


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


Occasional Contributor

Re: Conditional Format for the future Periods

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.




Community Boss

Re: Conditional Format for the future Periods

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