(New Power BI desktop user but long time programmer/Excel user.)
The underlying data set is a set of rows, one for each monthly status report. It contains the project ID, month/year of the report, and set of 'traffic light' statuses. For example:
ID Month Schedule Finance
C1 March 2019 G A
C1 April 2019 A A
C1 May 2019 A G
C2 March 2019 R R
C2 April 2019 G R
C2 May 2019 A R
I have created a matrix that has the project as the row and the monthly (say) Schedule status as the columns.
What I want to do for each row (project) is to show the number of months, starting with the most recent (right-hand most), that have the same status as the most recent report.
What I'm looking for is which projects have been reporting a run of A or a R statuses for the last few months.
From the above example, C1 would show that the schedule has been A for the last 2 months, and C2 finance has been R for the last 3 months as well as C2 schedule as A for 1 month. (If I also get to see the run of G then that is not a probelm - I can ignre those).
I'm not really familiar with the various functions that are available and I'm reall y not sure where to start. So any assistance would be appreciated.