I wanted to know if there's any way to compare your data with system date in order to auto generate the conditional calculated field formula instead of hard coding it after every month.
Like Currently I have to hard code the calculated field formula to obtain desired results i.e.
Sales'16 should be calculated by adding up all the previous month's sale with the current month's sale if the date has surpassed 15th of the month (SOP)
In current scenario, the date is 23rd Jan'2017 so the formula for calculating sales'16 would be:
Sales'16: [Apr'16] + [May'16] + [Jun'16] + [Jul'16] + [Aug'16] + [Sep'16] + [Oct'16] + [Nov'16] + [Dec'16] + [Jan'16]
In another scenario, Lets assume if the current date was 14th Jan'2017 so the formula for calculating sales'16 would have been:
Sales'16: [Apr'16] + [May'16] + [Jun'16] + [Jul'16] + [Aug'16] + [Sep'16] + [Oct'16] + [Nov'16] + [Dec'16]
Ratio: (SUM([Sales'17]) - SUM([Sales'16])) / SUM([Sales'16]) * 100
Please find the attached package workbook for your perusal.