Forum Discussion
Cumulative Unfinished Projects
That is a very different question and requires you to know all possible statuses (stati?) beforehand.
By the way,
the max status per project per quarter
is again a very different question. You would need to indicate what "max" means in your context.
The statuses are the same: Done, In Progress, Not Started.
I apologize as I thought it would solve both.
Basically if i added a third item C that changed statuses in the quarter, I would pull done for that project and quarter.
Both the table and graph would only show a max of 1 per project per quarter. You would take Done if Done, In Progress if applicable and otherwise not started. Once the project is done, it obviously would not continue and would be blank (same if the project first appears after the quarter)
Basically if i added a third item C that changed statuses in the quarter, I would pull done for that project and quarter.
ProjectFiscal Year QuarterStatus
| Project | Fiscal Year Quarter | Status |
| A | 2022Q2 | In Progress |
| A | 2022Q4 | Done |
| B | 2022Q3 | Not Started |
| C | 2022Q3 | Not Started |
| C | 2022Q3 | Done |
| Results | Table | Project | ||
| A | B | C | ||
| 2022Q2 | In Progress | |||
| 2022Q3 | In Progress | Not Started | Done | |
| 2022Q4 | Done | Not Started |
Thanks and sorry for the confusion.
Now we are moving into "shouldn't that rather be a calculated column?" territory. Are any of the statuses influenced by user interaction with the report?
I would provide the user the ability to filter on Project or Quarter using slicers.
I originally tried to do it using calculated columns but figured there might be a better solution for a large (and growing data set).
I couldn't simultaneously find the max per project per quarter and get the unfinished projects to continue until done.
the way I read your questions/intentions seems to require a materialization of project status per quarter - which is easiest done either in Power Query or as a calculated table in DAX. Let's say you wanted to give the user the option to select specific date ranges - that would have an impact on the "max" status in a quarter. But if they always only can select quarters to look at then the materialization would be sufficient.