Forum Discussion
Calculating days in status for active projects
Hi
Any help with this would be much appreciated!
I have the following tables:
Customer Table
| Customer | Project ID |
| A | 1001 |
| B | 1002 |
| C | 1003 |
| C | 1004 |
Project Table
| Project ID | Type | Start Date | End Date |
| 1001 | Full | 1/1/2023 | 31/10/2023 |
| 1002 | Full | 1/1/2023 | |
| 1003 | Full | 1/4/2023 | 30/6/2023 |
| 1004 | Partial | 1/7/2023 |
Task Table
| Task ID | Project ID | Task Type | Start Date | End Date |
| 111111 | 1001 | A | 1/1/2023 | 31/3/2023 |
| 111112 | 1001 | B | 1/4/2023 | 30/6/2023 |
| 111113 | 1001 | C | 1/7/2023 | 31/10/2023 |
| 111114 | 1002 | A | 1/1/2023 | 31/3/2023 |
| 111115 | 1002 | A | 1/1/2023 | 30/6/2023 |
| 111116 | 1002 | B | 1/7/2023 | |
| 111117 | 1003 | A | 1/4/2023 | 30/4/2023 |
| 111118 | 1003 | B | 1/5/2023 | 31/5/2023 |
| 111119 | 1003 | C | 1/6/2023 | 30/6/2023 |
| 111120 | 1004 | A | 1/7/2023 |
Task Requests
| Project ID | Task A | Task B | Task C |
| 1001 | 1 | 1 | 1 |
| 1002 | 2 | 1 | 1 |
| 1003 | 1 | 1 | 1 |
| 1004 | 1 | 1 | 1 |
I am looking to show for any active projects (i.e. no project end date) how long each stage has taken. There may be multiple of the same task required (outlined on task requests table). If all tasks haven't been completed it should be the difference between when the first of that task type was started and today, if all tasks have been completed it should be the difference between when the first of that task type was started and the date the final task of that type was completed.
The table below shows the expected outcome:
| Customer | Task A (days) | Task B (days) | Task C (days) |
| B | 180 (30/6* - 1/1) | 145 (Today - 1/7) | |
| C | 145 (Today - 1/7) |
*latest date for task type A to be completed
I was thinking of creating a calculated column for each stage but any guidance would be much appreciated.
Thanks!
2 Replies
Hi Anonymous ,
Below is my table1:
Below is my table2:
The following DAX might work for you:
task A = var a = 'Task Table'[End Date] - 'Task Table'[Start Date] var b = NOW() - 'Task Table'[Start Date] var result1 = IF('Task Table'[End Date]<>0&&'Task Table'[Task Type] = "A" ,a,b) var result = SWITCH( TRUE(), 'Task Table'[Project ID] = 1001 && 'Task Table'[Task Type] = "A" , result1, 'Task Table'[Project ID] = 1002 && 'Task Table'[Task Type] = "A" , result1, 'Task Table'[Project ID] = 1003 && 'Task Table'[Task Type] = "A" , result1, 'Task Table'[Project ID] = 1004 && 'Task Table'[Task Type] = "A" , result1 ) return resulttask B = var a = 'Task Table'[End Date] - 'Task Table'[Start Date] var b = NOW() - 'Task Table'[Start Date] var result1 = IF('Task Table'[End Date]<>0&&'Task Table'[Task Type] = "B" ,a,b) var result = SWITCH( TRUE(), 'Task Table'[Project ID] = 1001 &&'Task Table'[Task Type] = "B", result1, 'Task Table'[Project ID] = 1002 &&'Task Table'[Task Type] = "B" , result1, 'Task Table'[Project ID] = 1003 &&'Task Table'[Task Type] = "B", result1 ) return resulttask C = var a = 'Task Table'[End Date] - 'Task Table'[Start Date] var b = NOW() - 'Task Table'[Start Date] var result1 = IF('Task Table'[End Date]<>0&&'Task Table'[Task Type] = "C" ,a,b) var result = SWITCH( TRUE(), 'Task Table'[Project ID] = 1001 &&'Task Table'[Task Type] = "C" , result1, 'Task Table'[Project ID] = 1002 &&'Task Table'[Task Type] = "C" , result1, 'Task Table'[Project ID] = 1003 &&'Task Table'[Task Type] = "C" , result1 ) return resultcreate a measure:
task A = CALCULATE( MAX('Task Table'[task A]), FILTER('Customer Table','Customer Table'[Customer] = "B" || 'Customer Table'[Customer] = "C") )task B = CALCULATE( MAX('Task Table'[task B]), FILTER('Customer Table','Customer Table'[Customer] = "B" || 'Customer Table'[Customer] = "C") )task C = CALCULATE( MAX('Task Table'[task C]), FILTER('Customer Table','Customer Table'[Customer] = "B" || 'Customer Table'[Customer] = "C") )The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.