This can be done by blending the data. First you duplicate the data source. Rename them Date Opened and Check Date Then establish a relationship between the Date Opened and the Check Date at the year and month level. Now build the first two colums using the fields from the Date Opened source and then sum the total paid using the field from the Check Date source.I have included a workbook with this example.
Data Blending.twbx.zip 12.0 KB
Thank you very much for your response. Your response and example worked perfectly for the example that I gave you and I was able to re-create your solution in my workbook. I did run into an issue around filtering though. For the example that I provided, let's say I wanted to apply a filter to only see the claims that were litigated and let's say one of the claims that opened in January 2012 was litigated and one was not. When I applied the litigation filter, the number of claims that opened in January 2012 properly showed only one claim but the filter did not get properly applied to the total paid field.
Any ideas on how to get the filters to work properly?
Thank you again for your help!