Here is a simple 3 column dataset showing Categories, Date and Value

Catagorie |
Date |
Value |

Fish | 08-12-2015 | 6 |

Crab | 05-12-2015 | 7 |

Crab | 04-12-2015 | 6 |

Bird | 27-11-2015 | 4 |

Snow | 25-11-2015 | 10 |

Cat | 21-11-2015 | 7 |

Dog | 12-11-2015 | 5 |

Dog | 28-10-2015 | 5 |

Fish | 12-10-2015 | 3 |

Bird | 11-10-2015 | 9 |

Dog | 22-09-2015 | 9 |

Crab | 17-08-2015 | 8 |

Cat | 11-08-2015 | 1 |

Fish | 20-07-2015 | 5 |

Crab | 03-07-2015 | 2 |

Fish | 02-06-2015 | 8 |

Fish | 26-05-2015 | 9 |

Dog | 14-05-2015 | 4 |

Snow | 07-05-2015 | 7 |

Bird | 03-05-2015 | 9 |

Cat | 20-04-2015 | 10 |

Cat | 15-04-2015 | 2 |

Snow | 13-04-2015 | 3 |

Crab | 29-03-2015 | 5 |

Crab | 23-03-2015 | 2 |

Bird | 14-03-2015 | 5 |

Cat | 14-03-2015 | 1 |

Dog | 26-02-2015 | 9 |

Fish | 16-02-2015 | 4 |

Fish | 08-02-2015 | 6 |

Bird | 18-01-2015 | 1 |

Snow | 06-01-2015 | 10 |

The objective is to compute category wise average of values against the 5 most recent dates. So the expected result is:

Categorie |
Category wise average values on 5 most recent dates |

Bird | 5.60 |

Cat | 4.20 |

Crab | 5.60 |

Dog | 6.40 |

Fish | 6.20 |

Snow | 7.50 |

So for the Category of Fish (dates are sorted in descending order), the average should be 6.2 (average of 6,3,5,8,9)

Catagorie |
Date |
Value |

Fish | 08-12-2015 | 6 |

Fish | 12-10-2015 | 3 |

Fish | 20-07-2015 | 5 |

Fish | 02-06-2015 | 8 |

Fish | 26-05-2015 | 9 |

Fish | 16-02-2015 | 4 |

Fish | 08-02-2015 | 6 |

I have solved this problem using the PowerPivot. You may download my solution workbook from here.