Forum Discussion
Solved
Create new column for MAXDate
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.
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.
1 Reply
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.