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.
Hi,
Try:
If Date({date fied}) IN Date(DateAdd('m', - 1, {?EndDate}- Day({?EndDate})) + 1) TO
Date({?EndDate}- Day({?EndDate}))) then
-Abhilash
Your formula also doesn't account for different years if, for example, the value of {?EndDate} is 1/31/2016. So, I would try something like this:
Datevar endDt := {?EndDate}) - Day({?EndDate}); <--Gives the end day of the previous month
DateVar startDt := Date(Year(endDt), Month(endDt), 1); <--Gives the first day of the previous month
If Date({Date Field}) >= startDt and Date({Date Field}) <= endDt then...
-Dell
Add a comment