Compare previous months data

[Anonymous]

Hi Guys, Any suggestion on the below.

Need to pick the ID from current month and check if the performance is red for the past 3 months, and mark as Y in the current month if it is Red for the past 3 months continuously.

Output table must have only current month data. History data is only for comparison and it must not be present in the dashboard.

Data:

REPORTING_PERIOD	ID	Performance
30/11/2017	100	Green
31/12/2017	100	Red
31/01/2018	100	Red
28/01/2018	100	Red
31/01/2018	100	Red
31/12/2017	200	Green
31/01/2018	200	Red
28/01/2018	200	Red
31/01/2018	200	Red

Output:

REPORTING_PERIOD	ID	Performance	Past3Months Red Flag
31/01/2018	100	Red	Y
31/01/2018	200	Red	N

Thanks.