How to format based on the time buckets
I would like to share with you some knowledge on how to format specific time buckets.
- <relative> property of the time bucket
- Select required KF & Add to Multiple Selection
- Go to Property Selection, make your selection and add it to Multiple Selection as well.
Below selection will return the combination between KF and Current Time Bucket + 2 (you can check your selection in Multiple Selection Overview tab)
As current month is July the format is applied to September (July + 2).
I have tried with other operators (different than ‘=’) but, unfortunately, it didn`t work as expected. To have time range dependent format please check below workaround:
2. Excel workaround
I started from the requirement of having a yellow fill for Sales Fcst Price in [Current Month, Current Month + 3] time range
- I added some dummy entries for Current Date+3 and another row to return the Time Bucket Header date in the same format.
H15 points to Current Date + 3 using the following formula =DATE(YEAR(TODAY()), MONTH(TODAY())+3, 1)
- Then I inserted a Local EPM member DATE with the following calculation: =IF($H$15>H16,1,0)
- I added Conditional Formatting to Sales Fcst Price so that format to be applied when DATE equals 1
It works also w/o creating local member as below and point the conditional formatting to H18.
This trick could be applied also for locking cells in a specific time horizon.
If you know any other workaround please share!