cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AussieSusan
Frequent Visitor

Display longest run of same value

(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

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@AussieSusan ,

 


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

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 dataRaw data
Count highlighted 'A's for this projectCount highlighted 'A's for this project

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

Susan

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.