Hi, running PowerPivot in excel 2016, say I have a group of 10 persons, and for every day the 10 persons record a score. I'm wanting to find how many days (or rows given that each row is a different day for the individual person) since each person recorded a score of say 60. So in the example below, say today is 09/02, person3 would show as 2 rows or days and the other 2 persons would go back to whenever they last achieved 60 or above. I'm sure it will be a simple COUNTROWS with a filter for the person but I can't work it out. Thanks for any help!
Date | Person | Score |
08/02/14 | Person1 | 10 |
08/02/14 | Person2 | 13 |
08/02/14 | Person3 | 7 |
07/02/14 | Person1 | 15 |
07/02/14 | Person2 | 25 |
07/02/14 | Person3 | 65 |
06/02/14 | Person1 | 20 |
06/02/14 | Person2 | 15 |
06/02/14 | Person3 | 12 |
05/02/14 | Person1 | 10 |
05/02/14 | Person2 | 15 |
05/02/14 | Person3 | 12 |
Date | Person | Score |
08/02/14 | Person1 | 10 |
08/02/14 | Person2 | 13 |
08/02/14 | Person3 | 7 |
07/02/14 | Person1 | 15 |
07/02/14 | Person2 | 25 |
07/02/14 | Person3 | 65 |
06/02/14 | Person1 | 20 |
06/02/14 | Person2 | 15 |
06/02/14 | Person3 | 12 |
05/02/14 | Person1 | 10 |
05/02/14 | Person2 | 15 |
05/02/14 | Person3 | 12 |