Forum Discussion
Multi-condition table
Hi, Anonymous
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.
Hi, Anonymous
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.
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