The data I'm working with takes a weekly snapshot, but is reported monthly. Each week the same data is snapshot and retained, so in the the database I see the same events duplicated multiple times. I want to display a report using only the last date within each month.

The data set looks something like this:

Production Run Date

Snapshot Date (dd/mm/yyyy)

Person ID

Closed

201706

01/07/2017

1

0

201706

10/07/2017

1

0

201706

29/07/2017

1

0

201706

15/08/2017

1

1

201706

29/08/2017

1

1

Before I do any reporting, I'd like to filter out all records except the last one in each month on the snapshot date. This data can span years so I need to account for that as well. Any advice? I tried doing max date calculated fields but having issues keeping it isolated to the specific combination of year and month.

Almost, but when I do that it just returns the maximum date within the prod run, not the max date for each month in the prod run. So for the tables above I'd get only 1 record instead of 2 (one for july and one for august).

Ok, I did this. Please Check Screenshot and attached, You need to use Dateparse to convert String to Date. Then Write a FIXED Formulae to find out Maximum date and filter out Dates which are not Maximum.

Thanks

Deepak

If this Helps, Please take a Sec to mark my answer as Helpful and CORRECT and close Thread.

DateParse didn't seem to exist for me, managed to pull of the same result using DateName and adding together the month and the year. The rest worked and the filter is now working automatically rather than having to manually filter out dates (which doesn't work when data is updated into the future). Thank you for the assistance, much appreciated.