Multi-condition table

[Nedix]

Hello PBI community,

I have a problem which requires (re)building a table with multiple columns based on multiple conditions per column.

Problem statement:

- I have a database which contains multiple projects in the organization. Each project goes through 5 stages: 1 - Concept, 2 - Feasibility, 3 - Execution, 4 - Closure, Completed. Every update of the project, be it an update on the content of the phase or when changing the phase generates a new entry in the database, with its corresponding date of the update. This means that each project creates multiple entries in the database.

What I try to achieve:

- I want to create a new table which will have the following columns:

- 1. List of projects (unique)

- 2. Date when the project got into 1 - Concept

- 3. Days that the project stayed in 1 - Concept 

- 4. Date when the project got into 2 - Feasibility

- 5. Days that the project stayed in 2 - Feasibility 

- ... same logic for the rest of the phases

- I then want to find out:

- how many projects do I have in the pool (total)? how many projects do I have in each stage? 

- what is the actual velocity of the projects? how long do they stay in a certain stage

- what is the average velocity of the projects? how long do they stay on average in a certain stage

- what is the conversion rate relative to Stage 1 - Concept? e.g. how many projects made it to Stage 2, 3, 4, Completed (in percentage) - in the current data table the answer will be 100% since all made it to the end but I would like to know the logic/formula

- what is the conversion rate relative to the previous stage? e.g. how many projects from stage 1 - Concept made it to stage 2 - Feasibility, how many from Feasibility made it to Execution etc. - in the current data table the answer will be 100% since all made it to the end but I would like to know the logic/formula

- since some projects spill into the next year, I would like to show that some projects have been completed in 2021 while others in 2022

- how would everything above look like if I add one more dimension - Project type (Software, Hardware, Infrastructure)

Where I am today (and caveats):

- The output table is done manually, I visually inspect the data and copy/paste the date when the project changed stages

- I was able to get in PowerBI only Column 1 by creating a new table and using DISTINCT('Source'[Project name])

- I can get in XLS how many days the project stayed in a certain stage by subtracting one phase date from the next one (e.g. to get Column 3 (Days in 1 - Concept), I do Column 4 - Column 2. I need to find how to do this in PowerBI

- I think the logic for the output table could be something along the following line (only for Column 2. Date when the project got into 1 - Concept):

- find the project from Output(A2) in Source(A2)

- get the minimum date corresponding to stage 1- Concept

I hope I articulated the problem well enough and it would be great if someone could answer. I fully understand the logic, I just lack the skills to put it in practice.

Best,

M

Source table:

Project name	From Stage	To Stage	Date	Proj Mgr.
Alpha		1 - Concept	01/03/2021	John
Bravo		1 - Concept	01/04/2021	Mike
Alpha	1 - Concept	1 - Concept	10/03/2021	John
Alpha	1 - Concept	1 - Concept	12/03/2021	John
Charlie		1 - Concept	01/09/2021	Ann
Delta		1 - Concept	01/12/2021	Ann
Delta	1 - Concept	2 - Feasibility	01/12/2021	Ann
Delta	2 - Feasibility	3 - Execution	01/12/2021	Ann
Delta	3 - Execution	4 - Closure	20/12/2021	Ann
Delta	4 - Closure	Completed	20/01/2022	Ann
Alpha	1 - Concept	2 - Feasibility	02/04/2021	John
Alpha	2 - Feasibility	2 - Feasibility	20/04/2021	John
Bravo	1 - Concept	1 - Concept	05/04/2021	Mike
Bravo	1 - Concept	2 - Feasibility	10/05/2021	Mike
Charlie	1 - Concept	2 - Feasibility	01/11/2021	Ann
Alpha	2 - Feasibility	3 - Execution	05/05/2021	John
Alpha	3 - Execution	3 - Execution	09/09/2021	John
Alpha	3 - Execution	4 - Closure	10/10/2021	John
Charlie	2 - Feasibility	3 - Execution	15/12/2021	Ann
Charlie	3 - Execution	3 - Execution	10/01/2022	Ann
Charlie	3 - Execution	3 - Execution	20/01/2022	Ann
Charlie	3 - Execution	3 - Execution	01/03/2022	Ann
Bravo	2 - Feasibility	2 - Feasibility	15/05/2021	Mike
Alpha	4 - Closure	Completed	15/11/2021	John
Bravo	2 - Feasibility	2 - Feasibility	01/06/2021	Mike
Bravo	2 - Feasibility	1 - Concept	01/07/2021	Mike
Bravo	1 - Concept	1 - Concept	10/07/2021	Mike
Bravo	1 - Concept	2 - Feasibility	20/07/2021	Mike
Bravo	2 - Feasibility	3 - Execution	18/08/2021	Mike
Bravo	3 - Execution	3 - Execution	10/09/2021	Mike
Bravo	3 - Execution	4 - Closure	01/10/2021	Mike
Bravo	4 - Closure	Completed	15/10/2021	Mike
Charlie	3 - Execution	4 - Closure	10/04/2022	Ann
Charlie	4 - Closure	Completed	15/05/2022	Ann

Output table:

Project	1 - Concept	Days in Concept	2 - Feasibility	Days in Feasibility	3 - Execution	Days in Execution	4 - Closure	Days in Closure	Completed	PM
Alpha	01/03/2021	32	02/04/2021	33	05/05/2021	158	10/10/2021	36	15/11/2021	John
Bravo	01/04/2021	39	10/05/2021	100	18/08/2021	44	01/10/2021	14	15/10/2021	Mike
Charlie	01/09/2021	61	01/11/2021	44	15/12/2021	116	10/04/2022	35	15/05/2022	Ann
Delta	01/12/2021	0	01/12/2021	0	01/12/2021	19	20/12/2021	31	20/01/2022	Ann

[v-zhangti · Accepted solution]

Hi, @Nedix 

You can try the following methods.

Table = SUMMARIZE('Source table','Source table'[Project name],'Source table'[Proj Mgr.])

Column:

1 - Concept = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="1 - Concept"&&[Project name]=EARLIER('Table'[Project name])))

2 - Feasibility = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="2 - Feasibility"&&[Project name]=EARLIER('Table'[Project name])))

Days in Concept = [2 - Feasibility]-[1 - Concept]

Change the column format to integer.

3 - Execution = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="3 - Execution"&&[Project name]=EARLIER('Table'[Project name])))

Days in Feasibility = [3 - Execution]-[2 - Feasibility]

4 - Closure = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="4 - Closure"&&[Project name]=EARLIER('Table'[Project name])))

Days in Execution = [4 - Closure]-[3 - Execution]

Completed = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="Completed"&&[Project name]=EARLIER('Table'[Project name])))

Days in Closure = [Completed]-[4 - Closure]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nedix]

It's working almost as expected. The reason for saying "almost" is that I missed 3 scenarios:

1. A project can jump stages - for example project Echo entered 1 - Concept on the 1st of Jan and was moved directly into 3 - Execution on the 1st of Feb. This means it didn't go through 2 - Feasibility so the entry in 2 - Feasibility column is empty therefore columns Days in Concept and/or Days in Feasibility will fail the calculation

2. A project can enter directly in 3 - Execution, which means the 1 - Concept and 2 - Feasibility will be empty so the Days in Concept/Feasibility will fail the calculation

3. Since I am running the report/dashboard today, some projects may be in different stages - e.g. project Golf can be in 1 - Concept. In this scenario, Days in Concept should be Today() - [1 - Concept]

I think the scenarios above can be done with some IF nesting (or ISBLANK). I'll give it a go and see what comes up. 

Either way, thank you very much for the solution, it works like a charm. 

Best,

M