How to write an IF function so the boolean applies to more than one month
I am writing an IF Function (IF START()>CURRENTPERIOD() THEN TRUE ELSE FALSE) the result will be a Boolean. How do I make it apply to the next 6 Months or PERIODS? Thank you
Answers
-
Hi @iekpo
First thing to say is for booleans you don't need to write IF xxx then TRUE ELSE FALSE. you can just write IF xxx, and the result will either be TRUE or FALSE, it keeps the formula cleaner, it's more efficient and saves typing!!
In terms of the logic you need, keep this out of the main module and put the calculation in a separate module. See my article here for the reaons why:
So, in my example the Current Period is Oct-18
1. Create a Time Settings module, with two line items in
a. How many months forward? - formatted as a number, and enter the variable - in this case 6
b. Future Months Start - formatted as date, the formula should be ADDMONTHS(CURRENTPERIODSTART(), How many months forward?)
You could hard code the "6" as a parameter for the ADDMONTHS, but this approach allows for flexibility, in case you ever need to change the 6, to 9, for example
2. Create a Monthly Time Settings module with two line items, both formatted as Boolean:
a. Forecast Months?, with the following formula: START() >= CURRENTPERIODSTART()
b. Months in Future Range?, with the following formula: Forecast Months? AND START() < Time Settings.Future Months Start
3. Refer to this in your formula
IF Monthly Time Settings.Months in Future Range? THEN xxx ELSE yyy
A Time Settings module(s) should be the first module(s) you build and put all calculations and filter criteria that refer to time in here. It will simplify the model hugely as well as making the calculations much more efficient
I hope this is helpful
David
7
