Display longest run of same value

[Anonymous]

(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.

Susan

[v-yuta-msft]

@Anonymous ,

---

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 clear about the meaning of rows and columns you mentioned. In addtion, could you also clarify more about "show the number of months, starting with the most recent (right-hand most)"?

Regards,

Jimmy Tao

[Anonymous]

Thank you for your interest in my question. The real matrix looks like the first image.

The 2nd image highlights the values I'm trying to count for a particaulr project. As you can see the latest (the 'right-most column) is an 'A' and I want to know that there is a run of 8 'A' for this project.

(By the way, if a project does not have a value i the right-most column, then it can be skipped - the project has ended. However if the solution involves knowing the latest recorded value and using that, then no problem. I.e. knowing that project C00534 has a run of 5 'A's is not an issue.)

While I'm really only interested in the 'A' and 'R' values, getting a count of he run of statuses for any status is OK (i.e. knowing that C00513 has a run of  'G's is not a problem).

Susan

Raw data
Count highlighted 'A's for this project

[Anonymous]

I take it from the lack of response that this is not possible with Power BI?

Susan