I am trying to write a formula that uses a date parameter selected by the user to calculate a Last Month's MTD total. I am having trouble setting up the formula to handle when the dates are not in the same day range. For example, the parameter is 07/31/2016, but there is only 30 days in June (last month).
Parameter Date: 07/31/2016 ({?EndDate})
If DATE({date field}) >= DATE(YEAR({?EndDate}), (MONTH({?EndDate})-1,1) and
DATE ({date field}) <= DATE(YEAR({?EndDate}), MONTH({?EndDate}),1)-1) then
Result I want:
Data from range 06/01/2016 to 06/30/2016
The above formula will not work when the Parameter date is today's date. How can I change the formula to make it work for all instances? Thank you.