Calculating days in status for active projects

[metcala]

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!

[Anonymous]

Hi @metcala ,

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 result

task 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 result

task 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 result

create 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.

[metcala]

Hi

Thank you very much for the response.

Is there anyway that the switch statement could be dynamic i.e. from Project 1001 to XXXX. I have only included projects 1001-1004 as a representation of the dataset there are in fact a few hundred?

Thanks again