Forum Discussion
Help with Matrix table for Dates
Hi
Below is the sample table that I am using. I want to populate a roll up data using Matrix table or table for the no of projects from sharepoint list
| 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 |
| ProjectA | Task3 | 3-Mar-22 | 4-Mar-22 | 3-Mar-22 | 4-Mar-22 | Completed |
| ProjectA | Task4 | 4-Mar-22 | 5-Mar-22 | 4-Mar-22 | 5-Mar-22 | Completed |
| ProjectA | Task5 | 5-Mar-22 | 6-Mar-22 | 5-Mar-22 | 6-Mar-22 | Completed |
| ProjectA | Task6 | 6-Mar-22 | 11-Mar-22 | 6-Mar-22 | 11-Mar-22 | Completed |
| ProjectA | Task7 | 7-Mar-22 | 20-Mar-22 | 7-Mar-22 | In Progress | |
| ProjectA | Task8 | 8-Mar-22 | 25-Mar-22 | 8-Mar-22 | In Progress |
I want to show roll up data as below in Power BI . Actual finish should fill up only when all activities of ProjectA gets completed,till then it should empty..Is there any possible way to accomplish this. Thanks
| Project Name | Planned Start | Planned Finish | Actual Start | Actual Finish |
| ProjectA | 1-Mar-22 | 25-Mar-22 | 1-Mar-22 |
Anonymous
Looks like 4 measures
Min Planned Start = MIN ('Table'[Planned Start] )Max Planned Finish = MAX ('Table'[Planned Finish] )Min Actual Start = MIN ('Table'[Actual Start] )Max Actual Finish = VAR _Blanks = CALCULATE ( COUNTROWS ('Table'), ISBLANK ( 'Table'[Actual Finish] ) ) RETURN IF ( _Blanks = 0, MAX ('Table'[Actual Finish] ), BLANK() )You can change the names of the measures in the table to remove the Min and Max if you want.
3 Replies
Anonymous , A new column
new column =
var _cnt = countx(filter(Table, [Project Name] = Earlier([Project Name]) && isblank([Actual Finish]) ), [Project Name] )
return
if(isblank(_cnt), [Actual Finish], blank())
Anonymous
Looks like 4 measures
Min Planned Start = MIN ('Table'[Planned Start] )Max Planned Finish = MAX ('Table'[Planned Finish] )Min Actual Start = MIN ('Table'[Actual Start] )Max Actual Finish = VAR _Blanks = CALCULATE ( COUNTROWS ('Table'), ISBLANK ( 'Table'[Actual Finish] ) ) RETURN IF ( _Blanks = 0, MAX ('Table'[Actual Finish] ), BLANK() )You can change the names of the measures in the table to remove the Min and Max if you want.
