Cumulative Unfinished Projects

[bcobrien1977]

Hi,

I need both a bar graph and the underlying details (in a table) to show projects when they started until they finish, carrying over unfinished projects into the next quarter.

Basically the data looks at the original status, the new status and reflects the status until something changes.  I can figure out a measure but cannot seem to generate  a table that will give me projects until they reach done.  I am assuming that we are still in Q3 2023.  Can anyone please help me out? Thanks 

Project	Fiscal Year Quarter Start	Fiscal Year Quarter Completed	Status	New Status
A	2022 Q2	2022 Q4	In Progress	Done
B	2022 Q3		Not Started
Results Table
Project	Fiscal Year Quarter	Status
A	2022 Q2	Progress
A	2022 Q3	Progress
A	2022 Q4	Done
B	2022 Q3	Not Started
B	2022 Q4	Not Started
B	2023 Q1	Not Started
B	2023 Q2	Not Started

[bcobrien1977]

Okay, I originally tried to do two queries (1 a max per quarter per project and 1 per data and then tried to filter out only the matches. I am still am left with the original issue of how to carry over the unfinished projects. 

The Filter would just give you the active projects in a quarter so I don't see how that really complicates things but if I didn't give the option to filter, how would you approach it? 

[bcobrien1977]

This is amazing. Thanks you!  The final add is to get a count and sum of count if the value is populated. I cannot get the has one filter to work with these relationships.

Thanks

Brendan

[lbendlin]

what would the expected result look like?

[bcobrien1977]

Fiscal Year Quarter	Project	Status	Count
2022Q1	A		0
2022Q1	B		0
2022Q2	A	In Progress	1
2022Q2	B		0
2022Q3	A	In Progress	1
2022Q3	B	Not Started	1
2022Q4	A	Done	1
2022Q4	B	Not Started	1
2023Q1	A		0
2023Q1	B	Not Started	1
2023Q2	A		0
2023Q2	B	Not Started	1
2023Q3	A		0
2023Q3	B	Not Started	1
2023Q4	A		0
2023Q4	B	Not Started	1
Total			9

[lbendlin]

[bcobrien1977]

I love this solution but if I try to graph this measure, I get a different result. I am hoping again to have the max status per project per quarter and I get multiple values here because of the sumx.  The table looks just great but definitely would need to adjust to see this more visually?

Any thoughts.

Thanks

[lbendlin]

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.

[bcobrien1977]

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.

[lbendlin]

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?

[bcobrien1977]

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. 

[lbendlin]

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.

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[lbendlin]

Your source data is not in a usable format.  Should be more like

Project	Fiscal Year Quarter	Status
A	2022Q2	In Progress
A	2022Q4	Done
B	2022Q3	Not Started

Once you have that you can use a disconnected table with your quarters and for each quarter calculate the latest recorded status for each project.

[bcobrien1977]

Thanks, could you expand a bit more once I have those two tables? I just struggle with how to get the results in a table versus a simple bar graph. I really need the results table to show each quarter for each unfinished project. Thanks

[lbendlin]

see attached