Total days in status

[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 have received a partial solution however this was limited with a switch statement requiring hardcoded project IDs.

Thanks!

[metcala]

@ERD  Thanks so much, that worked perfectly!

[ERD · Accepted solution]

Hi @metcala, you can try the measure below:

daysAmt = 
var _t = 
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                Task,
                Customer[Customer],
                Project[Project ID],
                Project[End Date],
                Task[Task Type],
                Task[Start Date],
                Task[End Date]
            ),
            Project[End Date] = BLANK ()
        ),
        "_days",
            VAR _customer = CALCULATE ( MAX ( Customer[Customer] ) )
            VAR _taskType = CALCULATE ( MAX ( Task[Task Type] ) )
            VAR _checkIfBlank =
                CALCULATE (
                    COUNT ( Task[Task Type] ),
                    Customer[Customer] = _customer,
                    Task[End Date] = BLANK (),
                    Task[Task Type] = _taskType,
                    REMOVEFILTERS ()
                )
            VAR _maxDate =
                CALCULATE (
                    MAX ( Task[End Date] ),
                    Customer[Customer] = _customer,
                    Task[Task Type] = _taskType,
                    ALL ( Task[End Date] )
                )
            VAR _minDate =
                CALCULATE (
                    MIN ( Task[Start Date] ),
                    Customer[Customer] = _customer,
                    Task[Task Type] = _taskType,
                    ALL ( Task[Start Date] )
                )
            RETURN
                IF (
                    _checkIfBlank > 0,
                    DATEDIFF ( _minDate, TODAY (), DAY ),
                    DATEDIFF ( _minDate, _maxDate, DAY )
                )
    )
RETURN
    MAXX ( _t, [_days] )