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.
Compute the average of values against the 5 most recent dates of each Category
{ 0 Comments }