Conditional Format for the future Periods

CommunityMember82499

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.

 

Thanks

Sasikanth

DavidSmith

@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

David