Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need help to get a summarized output for each project with respect to its status.
Table
Project Name | Task Name | Planned Start | Planned Finish | Actual Start | Actual Finish | Activity status |
ProjectA | Task1 | 1-Mar-22 | 2-Mar-22 | 1-Mar-22 | 2-Mar-22 | Completed |
ProjectA | Task2 | 2-Mar-22 | 3-Mar-22 | 2-Mar-22 | 3-Mar-22 | Completed |
ProjectB | Task1 | 4-Mar-22 | 5-Mar-22 | 4-Mar-22 | 5-Mar-22 | Completed |
ProjectB | Task2 | 5-Mar-22 | 30-Mar-22 | 5-Mar-22 | In Progress | |
ProjectC | Task1 | 6-Mar-22 | 11-Mar-22 | Defered | ||
ProjectD | Task1 | 7-Mar-22 | 20-Mar-22 | Not Started | ||
ProjectD | Task2 | 8-Mar-22 | 25-Mar-22 | Not Started |
Output
Project Name | Planned Start | Planned Finish | Actual Start | Actual Finish | Project Status |
ProjectA | 1-Mar-22 | 3-Mar-22 | 1-Mar-22 | 3-Mar-22 | Completed |
ProjectB | 4-Mar-22 | 30-Mar-22 | 4-Mar-22 | In Progress | |
ProjectC | 6-Mar-22 | 11-Mar-22 | Defered | ||
ProjectD | 7-Mar-22 | 25-Mar-22 | Not Started |
Solved! Go to Solution.
@Anonymous,
Try these measures:
Planned Start =
CALCULATE ( MIN (Table1[Planned Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Planned Finish =
CALCULATE ( MAX (Table1[Planned Finish] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Actual Start =
CALCULATE ( MIN (Table1[Actual Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Actual Finish =
VAR vCountBlank =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
ISBLANK ( Table1[Actual Finish] )
)
VAR vActualFinish =
CALCULATE (
MAX ( Table1[Actual Finish] ),
ALLEXCEPT ( Table1, Table1[Project Name] )
)
VAR vResult =
IF ( ISBLANK ( vCountBlank ), vActualFinish, BLANK () )
RETURN
vResult
Project Status =
VAR vActualFinish = [Actual Finish]
VAR vTask =
CALCULATE (
MAX ( Table1[Task Name] ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
Table1[Actual Finish] = vActualFinish
)
VAR vResult =
CALCULATE (
MAX ( Table1[Activity status] ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
Table1[Task Name] = vTask
)
RETURN
vResult
Proud to be a Super User!
First Output table required
Project Id | Project Name | Planned Start | Planned Finish | Planned Hours | Actual Start | Actual Finish | Actual Hours | Status | %Complete |
6 | Project F | 11/5/2021 | 1/11/2022 | 136 | 11/5/2021 | 1/11/2022 | 198 | Completed | 100% |
23 | Project A | 2/10/2022 | 4/28/2022 | 1241 | 2/7/2022 | 629 | In Progress | 94% | |
2 | Project B | 5/2/2022 | 7/15/2022 | 340 | |||||
5 | Project E | 5/2/2022 | 6/15/2022 | 320 | Not Started | ||||
8 | Project G | 3/1/2022 | 5/27/2022 | 1759 | 3/1/2022 | 618 | In Progress | 54% | |
10 | Project H | 3/14/2022 | 4/20/2022 | 324 | 3/17/2022 | 217 | In Progress | 63% |
Project Id | Project Name | WBS | Planned Start | Planned Finish | Planned Hours | Actual Start | Actual Finish | Actual Hours | Status | %Complete |
10 | Project H | Sub Project 1 | 3/14/2022 | 3/18/2022 | 48 | 3/17/2022 | 3/29/2022 | 78 | Completed | 100% |
Sub Project 2 | 3/21/2022 | 3/25/2022 | 56 | 4/15/2022 | 8 | In Progress | 21% | |||
Sub Project 3 | 3/29/2022 | 4/1/2022 | 48 | 4/11/2022 | In Progress | 14% | ||||
Sub Project 4 | 4/4/2022 | 4/11/2022 | 108 | 3/31/2022 | 4/8/2022 | 107 | Completed | 100% | ||
Sub Project 5 | 4/13/2022 | 4/18/2022 | 16 | 4/19/2022 | 4/21/2022 | 16 | Completed | 100% | ||
Sub Project 6 | 4/18/2022 | 4/19/2022 | 16 | Not Started | 0% | |||||
Sub Project 7 | 4/19/2022 | 4/20/2022 | 16 | Not Started | 0% | |||||
Sub Project 8 | 4/19/2022 | 4/20/2022 | 16 | Not Started | 0% |
I have attached input and output table in the below location.
@Anonymous,
Try these measures:
Planned Start =
CALCULATE ( MIN (Table1[Planned Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Planned Finish =
CALCULATE ( MAX (Table1[Planned Finish] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Actual Start =
CALCULATE ( MIN (Table1[Actual Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )
Actual Finish =
VAR vCountBlank =
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
ISBLANK ( Table1[Actual Finish] )
)
VAR vActualFinish =
CALCULATE (
MAX ( Table1[Actual Finish] ),
ALLEXCEPT ( Table1, Table1[Project Name] )
)
VAR vResult =
IF ( ISBLANK ( vCountBlank ), vActualFinish, BLANK () )
RETURN
vResult
Project Status =
VAR vActualFinish = [Actual Finish]
VAR vTask =
CALCULATE (
MAX ( Table1[Task Name] ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
Table1[Actual Finish] = vActualFinish
)
VAR vResult =
CALCULATE (
MAX ( Table1[Activity status] ),
ALLEXCEPT ( Table1, Table1[Project Name] ),
Table1[Task Name] = vTask
)
RETURN
vResult
Proud to be a Super User!
Can some one help me , how to attach a excel to show my detialed table, so that I can get a help on my request.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |