Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
(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
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
I take it from the lack of response that this is not possible with Power BI?
Susan
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.