Grouping dates and getting totals

[PatchesPete]

I have data that looks similar to this:
Loc Emp Date Items
A 1 11/18/2014 15
A 2 11/18/2014 20
A 3 11/18/2014 84
A 1 11/25/2014 6
A 2 11/25/2014 13
A 3 11/25/2014 99
A 1 12/23/2014 52
A 2 12/23/2014 48
A 3 12/23/2014 43
A 1 12/30/2014 47
A 2 12/30/2014 12
A 3 12/30/2014 8

I need to take a sum of the items for only the last date of every month in the table. It's not necessarily the actual last day of the calendar month, but can be any date within the last week of the month. I don't need any of the other rows, just the last date of that month. The report is actually a rolling 12 month history but for purpose of this post, I only gave 2 months as a sample. So, I need the sum of 11/25/14 and the sum of 12/30/2014. What selection criteria would I use if I just want to pull in the last monthly date or do I need to pull all records in the table and then select the last monthly date using a formula? In either case, regardless what I write, I can't seem to get it to work. Report is grouped by Loc, then by date and that's where I start running into problems. Sure hope someone can help me out. Thanks in advance.

 

[pmax9999]

I did it this way.

Create the following formula:

[Code {AMonth}]
ToText(Year({Table.Date}),'#') + ToText(Month({Table.Date}),'#')
[/Code]

Create a Group on this formula and make it Group2. The Group Header and Footer can be suppressed.

Create a Running Total, to Sum {Table.Items}, and set it to evaluate on the following formula:

{Table.Date} = Maximum({Table.Date},{@Month})

The result of the running total will be the sum of all Items for the last date of the month.

Hope this helps.

Cheers
Pete

 

[PatchesPete]

Thanks much! I'll give it a try.