There are multiple ways to get that monthly sub total. One way is to define a self join relationship that matches one record to all records of the same month and year.
Here's a simple calculation to give you a value that can be used as the match field in such a relationship:
Define cMonth as: YourDateField - Day ( YourDateField ) + 1
Select Date as the result type.
Make a new Tutorial: What are Table Occurrences? of your table and link it by cMonth:
YourTable::cMonth = YourTable 2::cMonth
If you refer to your summary field via this expression: YourTable 2::summaryFieldName, you'll get the monthly subtotal. You can also use:
Sum ( YourTable 2::Field ) to get the monthly total for Field over all the records of the same month.
Ps: cMonth calculates the date for the first of the month for all dates of the same month and year.
Thank you! That worked perfectly!
I used Sum ( YourTable 2::Field )-Monthly goal to get the figure that I wanted after following the other steps. I made dummy records for two different months. The monthly goal field tracked the first month correctly and then reset to 0 at the start of the second month.
I'm starting to think there is nothing FMP can't do!