Create new column for MAXDate

[ElliotK]

Hello,

I wish to create a column that calculates that Max Data where the TaskPosition is "Completed" for TaskGroupID 13, within eID 53. 

ID	eID	TaskGroupID	TaskStatusID	TaskStatus	StartDate	EndDate	TaskPosition
1001	53	1	1	Processing	NULL	NULL
1002	53	2	1	Processing	NULL	NULL
1003	53	3	1	Processing	NULL	NULL
1004	53	4	2	Quality	NULL	NULL
1005	53	5	2	Quality	NULL	NULL
1006	53	6	2	Quality	NULL	NULL
1007	53	7	2	Quality	NULL	NULL
1008	53	8	2	Quality	NULL	NULL
1009	53	9	3	Issuance	NULL	NULL
1010	53	10	3	Issuance	NULL	NULL
1011	53	11	3	Issuance	NULL	NULL
1012	53	12	4	Signing	NULL	NULL
1013	53	13	4	Signing	NULL	01/08/2022	Completed

If there is a record after completed, do not show the max. 

ID	eID	TaskGroupID	TaskStatusID	TaskStatus	StartDate	EndDate	TaskPosition
1001	53	1	1	Processing	NULL	NULL
1002	53	2	1	Processing	NULL	NULL
1003	53	3	1	Processing	NULL	NULL
1004	53	4	2	Quality	NULL	NULL
1005	53	5	2	Quality	NULL	NULL
1006	53	6	2	Quality	NULL	NULL
1007	53	7	2	Quality	NULL	NULL
1008	53	8	2	Quality	NULL	NULL
1009	53	9	3	Issuance	NULL	NULL
1010	53	10	3	Issuance	NULL	NULL
1011	53	11	3	Issuance	NULL	NULL
1012	53	12	4	Signing	NULL	NULL
1013	53	13	4	Signing	NULL	01/08/2022	Completed
1013	53	13	4	Signing	Null	NULL

I wanted to do this as a column as I wish to calculate the difference between the very first start date and the very last end date where the status is completed. 

Any help appreciated. 

[v-stephen-msft · Accepted solution]

Hi @ElliotK ,

Please try

Column = CALCULATE(MAX('Table (2)'[EndDate]),FILTER('Table (2)',[TaskPosition]="Completed"&&[eID]=EARLIER('Table (2)'[eID])))

Best Regards,

Stephen Tao

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