I haven't tested these but the following logic should work
1. Write a formula to determine the Month to Date for comparison.
_MTDMonth = start_of_month(group_max(posting date)) - this returns me the start date for the current month.
2. Write a dynamic formula to return total for period.
$ MTD = sum_if(start_of_year(posting date) = _mtdmonth, $ amount) - this sums all transactions for the current month.
You could also use now() to create a month parameter.
MonthToDay=sum_if(start_of_month(posting_date)=CurrentMonth and posting_date < =date(now()), Amount)
PrevMonthToDay=sum_if(start_of_month(posting_date)=PreviousMonth and day_of_month(posting_date) <= day_of_month(now), Amount)
This can similarly be done to create a Year to Date or Prior year to date group/filter
You should really use today() rather than now().
today() is derived once per day and then cached, since it just returns the current date which is static for the entire day, whereas now() is derived every time it is called, since it returns the current timestamp which changes every time it is called, hence you should get better performance if you use today().
Rachel Kozloski I thought about 'This Month' as well, but I have found that depending on the data set, there may be values in the date in 'this month' but to be processed in the future. In the future, it may be advantageous to introduce a 'to date' keyword.
This could be something like this year to date. to date keyword would mean a time frame up to the day of month of today. This should then permit something like 'last year to date' and 'this year to date' adding date filters from the start of default/custom calendar to the day of month.
Hey Curtis Balusek, PhD. ,
"Month to date" , "Year to Date" , and "Week to date" are currently available keywords. Sounds like they would help you out! :)